How to use cursor variables, dynamic sql

[chelandrew]

I have a procedure that needs to select/sort through a table 10 times. The only thing different in each select is the 'where' and 'order by' clauses. I need to use a cursor to process but don't want to duplicate processing code for each cursor ten times since the routine is the same for each pass. Can I use a cursor variable to point to each of the selects or should I use dynamic sql? Can't seem to find many examples of cursor variables or using dynamic sql.

 

[cmmrfrds]

You can build you own sql strings something like, if this is what you are asking. try it in ISQLW.exe

declare @sql varchar(2000)
declare @wherestring varchar(50)
declare @orderstring varchar (50)
set @wherestring = 'where something = '
set @orderstring = 'order by '


set @sql = 'Select * from yourtable ' +
@wherestring + yourcriteria +
@orderstring + yourorder
exec (@sql)

 

[SpeedOLight]

This may give you an idea on using cursors...please note: There's no error checking in this code!!! One other thing, I know that dynamic SQL causes a small performance hit since the Stored Proc needs to re-determine which index to use with each select the data. I don't believe this same issue is true with the explicit declares.

IF @pintCase = 1
DECLARE CaseCursor CURSOR
FORWARD_ONLY STATIC FOR
Select Field1,
Field2
From Table1
Where Field1 = @pintSomeValue

Else
DECLARE CaseCursor CURSOR
FORWARD_ONLY STATIC FOR
Select Field1,
Field2
From Table1
Where Field2 = @pintSomeDifferentValue


OPEN CaseCursor

FETCH Next from CaseCursor
INTO @intField1Variable ,
@intField2Variable

Hope this helps...

Regards...Marc
Independent Software Consultant