定义游标时使用动态SQL

在定义游标时，我们一般是这样定义的：

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