CODEWITHSUNDEEP
sqlsql-servert-sql
user3760741
user3760741

Reputation: 163

Using local function parameter inside a while loop - SQL Server

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)

Upvotes: 0

Views: 401

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

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)

Upvotes: 1

Related Questions