How to print a table row wise in SQL Server?

debugcn Published at Dev

Jason Clark

I have created a function that outputs the table of a number, Here is my UDF scalar function:

create function fntable(@a int)
returns varchar(250)
as begin
declare @b int, @c varchar(250),@e varchar(50)=''
set @b=1
while (@b<=10)
begin
set @c=@a*@b
set @e=@e+@c
set @b=@b+1
end
return @e
end

It displays what i want, but it displays the result in a single row, as per my knowledge. I want to display the rows in multiple rows. How can i do this ? i want to print a table of a number.

lc.

You need a table-valued function. Using your code as is, this roughly translates to:

create function fntable(@a int)
returns @e table(c int)
as begin
    declare @b int = 1
    while (@b <= 10)
    begin
        insert into @e values (@a * @b)
        set @b += 1
    end

    return
end

Note, however, in this particular case the number of iterations is a constant 10 and the whole thing can be done inline in a single query, without a loop simply by manually expanding the loop. This can be then saved as an inline table-valued function, which can in turn be referenced from other queries.

create function fntable(@a int)
returns table as
return (
    select @a * 1 as c
    union all
    select @a * 2 as c
    union all
    select @a * 3 as c
    union all
    select @a * 4 as c
    union all
    select @a * 5 as c
    union all
    select @a * 6 as c
    union all
    select @a * 7 as c
    union all
    select @a * 8 as c
    union all
    select @a * 9 as c
    union all
    select @a * 10 as c
)

Collected from the Internet

Please contact [email protected] to delete if infringement.