Using local function parameter inside a while loop - SQL Server

Question

My inline table function basically prints out numbers, from 1 to the number that is input in the function. Since I'm new to SQL Server I am not sure how to implement a while loop inside a function. My implementation is below. As expected i get a lot compiler errors at WHILE, BEGIN, THEN. Can anyone suggest how can i improve the code. Thanks!

CREATE FUNCTION num_F(@nnn INT)
RETURNS TABLE
AS
RETURN (SELECT CASE 
                WHEN @nnn = 0
                    THEN NULL
                    ELSE WHILE dbo.num_F(@nnn) > 0
                          BEGIN
                          THEN dbo.num_F(@nnn)
                          SET dbo.num_F(@nnn) = dbo.num_F(@nnn)-1
                         END
                         GO
                END as First_Col
        ORDER BY First_Col ASC)

Pரதீப் · Accepted Answer · 2014-10-28 06:57:34Z

1

simplest ways is by using a common table expression (CTE)

CREATE FUNCTION Numfunctn1 (@num INT)
RETURNS TABLE
AS
    RETURN(
      WITH cte
           AS (SELECT 1 id
               UNION ALL
               SELECT id + 1
               FROM   cte
               WHERE  id < @num)
      SELECT *
       FROM   cte) 


SELECT *
FROM   Numfunctn1(10)

edited Oct 28, 2014 at 6:57

answered Oct 28, 2014 at 5:38

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

user3760741 Over a year ago

thanks but is this an inline table function ?. Can you help to form an inline table function

Pரதீப் Over a year ago

@user3760741 check now

Collectives™ on Stack Overflow

Using local function parameter inside a while loop - SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related