如何从while循环创建sql server cte
我这样的循环
我这样的循环
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
解决方法
如果你需要表：
;WITH Sec(Number) AS
(
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM Sec
WHERE Number < 884
)
SELECT * FROM Sec
OPTION(MAXRECURSION 0)
如果你需要一个字符串：
;WITH Sec(Number) AS
(
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM Sec
WHERE Number < 884
)
SELECT STUFF(a.[Str],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)
