Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating CURSOR Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2008 2:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi All,
I have a situation where I need to create a cursor based on condition. Here is my code:

IF @bCustom = 0
SET @SQLCmd =
'SELECT * FROM TableA
ELSE
SET @SQLCmd =
'SELECT * FROM TableB
DECLARE RandomUser_CURSOR CURSOR
LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS
FOR
@SQLCmd
FOR UPDATE
OPEN RandomUser_CURSOR
FETCH NEXT FROM RandomUser_CURSOR INTO
@Col1, @Col2, @Col3
WHILE @@FETCH_STATUS = 0
BEGIN

It gives me "Incorrect syntax near '@SQLCmd'"

Any help will be greatly appreciated.

Thanks.
Post #496672
Posted Wednesday, May 7, 2008 2:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 23,029, Visits: 31,547
First, only partial code in your post, so not a real help. Second, why do you need to create a cursor dynamically? What are you try to accomplish?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #496678
Posted Wednesday, May 7, 2008 2:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi SS,
The rest of the code is as you can assume:

END
FETCH NEXT FROM RandomUser_CURSOR INTO
Close and Dealloc cursor.

The point here is how can I run @SQLCmd variable within a cursor. I need to update records.

Thanks.
Post #496687
Posted Wednesday, May 7, 2008 2:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
SQL doesn't let you use variables that way.

What I would do is (a) figure out if a cursor is really needed for this or if it can be replaced with set-based code, (b) if a cursor is needed, create it with a Union All query based on a Where, instead of using If.

DECLARE RandomUser_CURSOR CURSOR
LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS
FOR
select *
from TableA
where @bCustom = 0
union all
select *
from TableB
where @bCustom != 0
FOR UPDATE
OPEN RandomUser_CURSOR
FETCH NEXT FROM RandomUser_CURSOR INTO
@Col1, @Col2, @Col3
WHILE @@FETCH_STATUS = 0
BEGIN



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #496693
Posted Wednesday, May 7, 2008 2:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 23,029, Visits: 31,547
NEI (Not Enough Information). Still don't know what you are trying to accomplish, plus the code I see just looks wrong from the get go. Plus, you have variables in your posted code that I don't see any declaration for.

Again, what are you try to accomplish? A good explaination, table DDL, sample data, and expected results would really help you get the help you need to meet your objectives.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #496697
Posted Wednesday, May 7, 2008 2:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi Ten,
That is a great idea, except that I have a variable in the order by clause like:

ORDER BY d.PurchaseOrderQty ' + @OrderBy + ' ,UserNumeric1'

So I would have to wrap the SQL in a variable. Right?

Thanks.
Post #496706
Posted Thursday, May 8, 2008 1:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Not sure what you mean by wrapping it in a variable, but if you mean you would need to turn it into dynamic SQL, then the answer is "maybe". It depends on how dynamic it is.

For example:

order by
case
when @Orderby = 'Account' then Account
when @Orderby = 'OrderDate' then cast(OrderDate as int)
end

You can do that kind of thing, so long as the columns are the same data type, or can be cast/converted to the same data type.

If, however, your @Orderby variable is some long list of columns, then yeah, you'll need to use dynamic SQL for that. Just be very careful about SQL injection if you do that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #497390
Posted Thursday, May 8, 2008 1:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi GSquared,
Why didn't I think of that? You are a true SQL developer. Thank you for your time.
Post #497402
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse