Returns a result set with rows between a specified start and end value.
sa_rowgenerator ( [ rstart [, rend [, rstep ] ] ])
rstart This optional integer parameter specifies the starting value. The default value is 0.
rend This optional integer parameter specifies the ending value. The default value is 100.
rstep This optional integer parameter specifies the increment by which the sequence values are increased. The default value is 1.
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Column name |
Data type |
Description |
|---|---|---|
row_num |
integer |
Sequence number. |
The sa_rowgenerator procedure can be used in the FROM clause of a query to generate a sequence of numbers. This procedure is an alternative to using the RowGenerator system table. You can use sa_rowgenerator for such tasks as:
Generating test data for a known number of rows in a result set.
Generating a result set with rows for values in every range. For example, you can generate a row for every day of the month, or you can generate ranges of PostalCodes.
Generating a query that has a specified number of rows in the result set. This may be useful for testing the performance of queries.
You can emulate the behavior of the RowGenerator table with the following statement:
SELECT row_num FROM sa_rowgenerator(1255)
None
The following query returns a result set containing one row for each day of the current month:
SELECT dateadd(day,row_num-1, ymd(datepart(year,CURRENT DATE), datepart(month,CURRENT DATE), 1)) AS day_of_month FROM sa_RowGenerator(1,31,1) WHERE datepart(month,day_of_month) = datepart(month,CURRENT DATE) ORDER BY row_num
The following query shows how many employees live in zip code
ranges (0-9999), (10000-19999), ..., (90000-99999). Some of these
ranges have no employees, which causes the warning Null
value eliminated in aggregate function (-109).
The sa_rowgenerator procedure can be
used to generate these ranges, even though no employees have a PostalCode
in the range.
SELECT row_num AS r1, row_num+9999 AS r2, count(PostalCode) AS zips_in_range FROM sa_rowgenerator(0,99999,10000) D LEFT JOIN employees ON PostalCode BETWEEN r1 AND r2 GROUP BY r1, r2 ORDER BY 1
The following example generates 10 rows of data and inserts them into the emp table:
INSERT INTO emp1(dept_id, salary, name) SELECT row_num, CAST( rand() * 1000 AS INTEGER), 'Mary' FROM sa_rowgenerator(1, 10)
