Table of Numbers

,

A user-defined function that returns a table of numbers between a and b (using the most efficient way I can think of).

e.g.  select * from table_of_numbers(1, 4) gives a table with rows 1, 2, 3 and 4.

CREATE FUNCTION table_of_numbers (@min_number int, @max_number int)
RETURNS @t table (i int)
AS
begin
  declare @number_of_numbers int
  set @number_of_numbers = @max_number - @min_number + 1
  insert into @t values (0)

  declare @i int
  set @i = 1
  while @i < @number_of_numbers / 2 + 1
  begin
    insert into @t select i + @i from @t
    set @i = @i * 2
  end
  insert into @t select i + @i from @t where i + @i < @number_of_numbers
  update @t set i = i + @min_number

  return
end

Rate

5 (1)

Share

Share

Rate

5 (1)