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…
generic xanax buy xanax online paypal money order - buy xanax online with prescription
Anonymous said…
generic xanax generic xanax bars white - xanax drug test employment
Anonymous said…
xanax online pictures of xanax generic pills - xanax generic peach
Anonymous said…
xanax drug buy xanax online in the uk - xanax generic colors
Anonymous said…
buy tramadol online order tramadol online visa - effects of tramadol high
Anonymous said…
buy tramadol online tramadol nausea - overdose of tramadol for dogs
Anonymous said…
cheap tramadol buy generic tramadol no prescription - tramadol withdrawal flu like symptoms
Anonymous said…
buy carisoprodol carisoprodol xanax interaction - order vicodin online no prescription
Anonymous said…
buy xanax without prescriptions xanax generic bars - do xanax show up drug test
Anonymous said…
xanax online xanax generic greenstone - drug interactions xanax and nyquil
Anonymous said…
buy carisoprodol cod carisoprodol drug classification - medication soma carisoprodol
Anonymous said…
buy tramadol online buy tramadol cheap no prescription - tramadol dosage women
Anonymous said…
xanax online xanax side effects for men - generic xanax interactions
Anonymous said…
generic xanax xanax bars effects erowid - generic xanax extended release
Anonymous said…
buy alprazolam online without prescription xanax 93833 - 20 mg xanax dosage
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 get you high - do people buy tramadol
Anonymous said…
buy cialis online generic cialis 200mg - cialis 2.5 mg daily use
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…
buy xanax online xanax dosage wikipedia - 2mg xanax get high
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…
xanax online easy buy xanax mexico - buy xanax online overnight delivery
Anonymous said…
buy tramadol hcl tramadol 900 mg - tramadol long term effects
Anonymous said…
http://landvoicelearning.com/#44827 buy cheap tramadol online no prescription - tramadol youtube
Anonymous said…
buy tramadol tramadol overdose yahoo answers - tramadol to buy online
Anonymous said…
buy tramadol overnight tramadol er - tramadol 40 mg
Anonymous said…
buy tramadol online mexican tramadol 100mg - tramadol 200 mg high
Anonymous said…
tramadol 100mg tramadol 50mg buzz - tramadol hcl sleep
Anonymous said…
learn how to buy tramdadol tramadol hcl xl - tramadol ultram ingredients
Anonymous said…
buy discount tramadol buy tramadol tennessee - 3 50 mg tramadol
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 tramadol tramadol an 627 online - tramadol withdrawal after 2 weeks
Anonymous said…
buy tramadol online tramadol cheap - tramadol hcl withdrawal
Anonymous said…
ativan for anxiety buy cheap ativan online - ativan withdrawal paranoia
Anonymous said…
buy tramadol in florida can buy tramadol online - tramadol nice high
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…
http://reidmoody.com/#49853 ativan and drinking alcohol - ativan side effects/ringing in the ears
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…
http://staam.org/#56274 tramadol 50mg usa - tramadol for dogs no rx
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 xanax online xanax bars sale online - xanax for panic attacks
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…
buy xanax online xanax 2mg sunset yellow - xanax online no prescription canada
Anonymous said…
http://ranchodelastortugas.com/#61301 xanax overdose alcohol - xanax 10 panel drug test
Anonymous said…
http://ranchodelastortugas.com/#30416 buy xanax no prescription mastercard - xanax 1mg side effects
Anonymous said…
buy xanax online can get xanax online - how to buy xanax online
Anonymous said…
buy xanax online cheap mixing klonopin and xanax and alcohol - do people buy xanax online
Anonymous said…
buy xanax online what does xanax pills do to you - order xanax online no prescription canada