CODEWITHSUNDEEP
sql-server
Jagdish
Jagdish

Reputation: 1

Error in Declaration of cursor

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?

Upvotes: 0

Views: 375

Answers (2)

barsh
barsh

Reputation: 470

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

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166376

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)

Upvotes: 0

Related Questions