Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

create while loop with cte

how to create sql server cte from a while loop
my loop like this

  declare @ind as int
  declare @code as nvarchar
  set @ind  = 0
   while @ind < 884
  begin
    select @ind  = @ind  + 1
    --here execute Procedure 
        --and set return value to variable 
    set @code = cast (@ind   as nvarchar)
  end
like image 553
soheil bijavar Avatar asked Oct 17 '12 07:10

soheil bijavar


2 Answers

If you need table:

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT * FROM Sec
OPTION(MAXRECURSION 0)

If you need one string:

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT STUFF(a.[Str], 1, 1, '')
FROM
(
    SELECT (SELECT ',' + CAST(Number AS NVARCHAR(3)) 
    FROM Sec
    FOR XML PATH(''), TYPE
    ).value('.','varchar(max)') AS [Str] 
) AS a
OPTION(MAXRECURSION 0)
like image 172
Dis Shishkov Avatar answered Oct 25 '22 22:10

Dis Shishkov


Below query selects values from 0 to 884:

;WITH T(Num)AS
(
    SELECT 0 
    UNION ALL
    SELECT Num+1 FROM T WHERE T.Num < 884
)SELECT Num FROM T 
OPTION (MAXRECURSION 0);
like image 37
TechDo Avatar answered Oct 25 '22 22:10

TechDo