User defined function with while loop in SQL Server

Question

I am asked to create a user defined function in SQL Server to returns the following pattern (for example, if the input = 5):

*****
 ****
  ***
   **
    *

Here is my code:

alter function udf_star (@input int)
returns varchar (200)
as 
begin 
    declare @star int 
    set @star = @input 

    declare @space int 
    set @space = 0

    while @star > 0
    begin 
        declare @string varchar (200)
        set @string = replicate (' ', @space) + replicate ('*', @star)

        set @star = @star - 1
        set @space = @space + 1  
    end 

    return @string 
end

When I execute the function

select dbo.udf_star (5)

it only shows

'    *'

(4 spaces + 1 star); can anyone points out how should I correct the syntax?

Thanks in advance!

. .(1) Use the recursive CTE that I suggested in your previous answer; (2) You seem to want to return a table, not a scalar value. — Gordon Linoff
– Gordon Linoff, Commented Apr 9, 2020 at 16:25
Thanks @GordonLinoff, yes, CTE is a good solution, but I am trying different SQL tools and wondering how I can achieve this by UDF. — Kay
– Kay, Commented Apr 9, 2020 at 16:37

John Cappelletti · Accepted Answer · 2020-04-09 16:48:36Z

2

It seems you may want a Table-Valued Function.

Also, loops should be avoided when possible

Example

CREATE FUNCTION [dbo].[tvf-Star] (@Input int)
Returns Table 
As
Return (  

Select Top (@Input) 
       Stars = replicate(' ',@Input-N)+replicate('*',N)
 From ( Select Top (@Input) N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1 ) A
 Order By N Desc
)

If you were to :

Select * from [dbo].[tvf-Star](5)

The Results

Stars
*****
 ****
  ***
   **
    *

answered Apr 9, 2020 at 16:48

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

User defined function with while loop in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related