SQLTeam.com | Weblogs | Forums

Convert number to Rows


#1

I need Function
Parameter @No = 5
Result
Table
No

1
2
3
4
5


#2

Try this:

create function dbo.fntally(@no int)
   returns table as
      return (select top(@no) row_number() over(order by (select null)) as n
                from             (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t1(n) /*     10 */
                     cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t2(n) /*    100 */
                     cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t3(n) /*  1,000 */
                     cross apply (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t4(n) /* 10,000 */
             )
;

then you can use function as a table:

select *
  from dbo.fntally(5) as a
;