Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating CURSOR


Creating CURSOR

Author
Message
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 690
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.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
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?

Cool

Cool
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)
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 690
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.
GSquared
GSquared
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15799 Visits: 9729
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
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
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.

Cool

Cool
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)
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 690
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.
GSquared
GSquared
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15799 Visits: 9729
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
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 690
Hi GSquared,
Why didn't I think of that? You are a true SQL developer. Thank you for your time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search