Simulating a pipeline function in SQL Server

When doing database development, many a times there is a requirement to be able to generate a list of running sequential numbers. One of such scenarios is the handling of comma separated lists that we had discussed in one of our blog posts before. We had shown how the pipeline function in Oracle can be used for that purpose. The equivalent of the pipeline function in the case of SQL Server would have to be a UDF (User Defined Function).   Such a UDF will take in two parameters: a) The number of rows to generate, and b) The starting number from which the list of sequential running numbers needs to be generated.

/**********************************************************************************************************
NAME: UDF_GET_SEQUENCE: For generating the sequential numbers in SQL Server
***********************************************************************************************************/
IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'UDF_GEN_SEQUENCE'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
PRINT 'Table Function UDF_GEN_SEQUENCE already exists...Dropping it and recreating'
DROP FUNCTION UDF_GEN_SEQUENCE
END
ELSE
BEGIN
PRINT 'Creating function UDF_GEN_SEQUENCE'
END
GO
CREATE function dbo.UDF_GEN_SEQUENCE
(
@Nbr_Of_Rows int,
@Start_Nbr int
)
returns @table table
(
Row_Num int not null primary key,
Data_Value int
)
as
begin
/* Seed the table with the initial value */
insert @table values (1, @Start_Nbr)

/* Now, loop through and create the list
till the rowcount of the inserts is
more than 0
*/
while @@ROWCOUNT > 0
begin
insert into @table (Row_Num, Data_Value)
select t1.Row_Num + t2.Max_Row_Num,
@Start_Nbr + t1.Row_Num + t2.Max_Row_Num - 1
from @table as t1
cross join
(select max (Row_Num) Max_Row_Num from @table) t2 /*derived table*/
where t1.Row_Num <= @Nbr_Of_Rows - t2.Max_Row_Num
end
return
end
GO

Using this function, one can now achieve a list of the running sequential numbers. Here is an example:

Say, I want a running list of 1000 numbers beginning with 215, here is the execution:

select * from dbo.UDF_GEN_SEQUENCE (1000, 215)

Abridged Output:

Row_Num Data_Value
——– ———–
1 215
2 216
3 217
4 218
5 219
6 220
7 221
8 222
9 223
.
.
.
998 1212
999 1213
1000 1214

This will work fine in both SQL Server 2000 as well as SQL Server 2005.  The same can be achieved by using a CTE in SQL Server 2005 – one would need to recurse through till the limit is reached but if you need a solution that will work for both SS2k and SQL 2005, the UDF above will work fine.