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?
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
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
