Error in Declaration of cursor

Question

set @SQL=N' select  @minTableId   = MIN(id)  from ' + @AcDB + '.dbo.vTblOfRollNo '

Declare Cursor For
EXEC SP_EXECUTESQL @SQL 

if i have declared all the variables in above query but Declaration of cursor in above query shows ERROR. What is Solution?

Answer 1

I dont think you need a cursor for this

try

DECLARE @AcDB VARCHAR(10),
        @Sql NVARCHAR(MAX)

set @SQL=N' select MIN(id)  from ' + @AcDB + '.dbo.vTblOfRollNo '

DECLARE @Temp TABLE(
        MinID INT
)
INSERT INTO @Temp EXEC SP_EXECUTESQL @SQL 

DECLARE @minTableId INT
SELECT TOP 1 @minTableId = MinID FROM @Temp

SELECT @minTableId

EDIT: Also here is the actual CURSOR documentation

DECLARE CURSOR (Transact-SQL)

Answer 2

In order to execute a cursor over dynamic SQL you must put the output of your dynamic sql into a temporary table and then cursor over the temporary table like this:

DECLARE @TableName NVARCHAR(100)
DECLARE @SQL NVARCHAR(1000)
CREATE TABLE #TempTABLE(email NVARCHAR(200))

SET @TableName='Users'
SELECT @SQL='INSERT INTO #TempTable SELECT email FROM ' + @TableName 
EXEC (@SQL)

DECLARE MyCursor CURSOR FOR SELECT * FROM #TempTable
OPEN MyCursor
DECLARE @Email NVARCHAR(200)
FETCH NEXT FROM MyCursor INTO @Email

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT 'Email = ' + @Email
 FETCH NEXT FROM MyCursor INTO @Email
END

CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE #TempTABLE