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.

Comments

Anonymous said…
generic xanax xanax 1 mg blue pill - xanax bars vs pies
Anonymous said…
xanax online pictures of xanax generic pills - xanax generic peach
Anonymous said…
buy tramadol online order tramadol online visa - effects of tramadol high
Anonymous said…
cheap tramadol buy generic tramadol no prescription - tramadol withdrawal flu like symptoms
Anonymous said…
buy tramadol online buy tramadol cheap no prescription - tramadol dosage women
Anonymous said…
cheap tramadol online tramadol hcl classification - buy tramadol online usa
Anonymous said…
carisoprodol drug carisoprodol other drugs in same class - carisoprodol 350 mg review
Anonymous said…
buy tramadol online tramadol hcl 50mg tablet amnea - tramadol withdrawal what to do
Anonymous said…
buy tramadol online no prescription rimadyl vs tramadol for dogs - buy tramadol and soma
Anonymous said…
tadalafil no prescription generic cialis tadalafil best buys - cialis вики
Anonymous said…
xanax online xanax xr 1mg high - xanax for anxiety and panic attacks
Anonymous said…
buy cialis online generic cialis viagra online - cheap generic cialis usa
Anonymous said…
buy tramadol tramadol overdose yahoo answers - tramadol to buy online
Anonymous said…
buy tramadol overnight tramadol er - tramadol 40 mg
Anonymous said…
tramadol 100mg tramadol 50mg buzz - tramadol hcl sleep
Anonymous said…
http://buytramadolonlinecool.com/#91646 tramadol high mg - tramadol 50 mg and alcohol
Anonymous said…
buy tramadol buy tramadol without rx - tramadol high many
Anonymous said…
http://landvoicelearning.com/#38471 tramadol 50mg capsules - buy tramadol online echeck
Anonymous said…
buy tramadol online cod tramadol 50 mg lexapro - buy tramadol no prescription cod
Anonymous said…
buy ativan online generic ativan buy - ativan dosage erowid
Anonymous said…
buy ativan online ativan vicodin overdose - buy lorazepam online us
Anonymous said…
http://reidmoody.com/#79166 ativan dosage per kg - side effects from lorazepam 1mg
Anonymous said…
cheap tramadol tramadol 100mg tabs - buy tramadol ultram ultracet online
Anonymous said…
order tramadol online tramadol dosage rabbits - 100mg tramadol too much
Anonymous said…
buy tramadol online buy tramadol online no prescription next day delivery - tramadol withdrawal effexor
Anonymous said…
Nefarious Rhinoceros - a large and stalwart animal. he did not as weighty as the bloodless rhinoceros, but quieten powerful - reaches the power 2-2, 2 m, lengths of up to 3, 15 m in zenith shoulders of 150-160 cm.
Anonymous said…
buy tramadol online buy tramadol 100mg - cheap tramadol no prescription needed
Anonymous said…
buy tramadol with mastercard tramadol 50mg tablets cost - tramadol hcl 325 mg
Anonymous said…
buy tramadol online order tramadol echeck - tramadol for dogs no prescription
Anonymous said…
http://ranchodelastortugas.com/#61301 xanax overdose alcohol - xanax 10 panel drug test