MSSQL 2005 - Cursor and Dynamic Queries

More about MSSQL 2005 and databases.

How do you built a T-SQL which uses cursor on dynamic queries ?

Dynamic queries means that the query statements are built based on variables. For instance ....

Direct Query

SELECT recID from TBL_Name WHERE deletedDate is null

Simple Dynamic Query

Declare @MyName as varchar(50)
SET @MyName='brandon'
SELECT recID from TBL_NAME WHERE MyName=@MyName

This is still fine. But what about the table name is depending on a variable ?

SET QUOTED_IDENTIFIER OFF
Declare @MyName as varchar(50),@MyTable as varchar(50),@DynQuery as varchar(8000)
SET @MyName='brandon'
SET @MyTable='TBL_NAME'
SET @DynQuery = "SELECT recID from" + MyTable + " WHERE MyName='" + @MyName + "'"
EXEC (@DynQuery )

ok, how do you put a dynamic query into a cursor ?

Here is the solution to make use of sp_executesql

SET QUOTED_IDENTIFIER OFF

declare @query nvarchar(max), @number int, @mainCursor cursor

set @query = " set @cursor = cursor for SELECT * FROM [ProjectT].[dbo].[TBL_SysUser]" + " open @cursor"

exec sp_executesql @query,N'@cursor cursor output',@mainCursor output

declare @recID as bigint,@myUserName as varchar(50)

fetch next from @maincursor into @recID,@myUserName,@myFirstName

while (@@fetch_status <> -1)
begin
print @recID
fetch next from @maincursor into @recID,@myUserName
end

More complicated sample

SET QUOTED_IDENTIFIER OFF

declare @linkedSrv as varchar(50)
declare @tempUserID as bigint
declare @query nvarchar(max), @number int, @mainCursor cursor

set @tempUserID='123456'
set @linkedSrv='local_branch1'
set @query = " set @cursor = cursor for SELECT recID FROM " + @linkedSrv + ".[ProjectT].[dbo].[TBL_User] WHERE myUserID=" + cast(@tempUserID as varchar(50))
+ " open @cursor"

exec sp_executesql @query,N'@cursor cursor output',@mainCursor output

declare @recID as bigint
fetch next from @maincursor into @recID

while (@@fetch_status <> -1)
begin
print @recID
fetch next from @maincursor into @recID
end

Thanks to this forum for the heads-up.