在定义游标时,我们一般是这样定义的:
DECLARE cur CURSOR FOR SELECT * FROM tableName
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cur
END
CLOSE cur
DEALLOCATE cur
但是有时候需要用到动态SQL,一般情况下我们想到的方法是:
DECLARE @sql nvarchar(1000)
SET @sql='SELECT * FROM tableName '
DECLARE cur CURSOR FOR
EXECUTE sp_executesql @sql
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cur
END
CLOSE cur
DEALLOCATE cur
但是这种写法是错误的,正确的应该是:
DECLARE @sql nvarchar(1000)
SET @sql='DECLARE cur CURSOR FOR SELECT * FROM tableName'
EXECUTE sp_executesql @sql
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cur
END
CLOSE cur
DEALLOCATE cur
我遇到了这个问题,通过搜索解决了问题,现在写出来与大家分享,我也是学人家的。