在这里用CTE是不行的,可以改用表变量,将CTE中的数据存到表变量中去,如下(红字为新增部分):
DECLARE @TempTable Table
(
P_Name varchar(20),
Prefix varchar(2)
);
with tmpCTE AS
(
select P_Name,Prefix from dbo.Provider
)
INERT INTO @TempTable SELECT * FROM tmpCTE;
declare @P_Name varchar(20);
declare @Prefix varchar(2);
DECLARE MyCursor CURSOR FOR SELECT P_Name,Prefix from @TempTable ;
OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @P_Name,@Prefix
WHILE @@FETCH_STATUS = 0
BEGIN
print @P_Name;
print @Prefix;
FETCH NEXT FROM MyCursor INTO @P_Name,@Prefix
END
游标中使用CTE
最新推荐文章于 2022-01-04 14:30:23 发布