May 7, 2008 at 2:16 pm
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.
May 7, 2008 at 2:23 pm
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?
😎
May 7, 2008 at 2:32 pm
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.
May 7, 2008 at 2:38 pm
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
May 7, 2008 at 2:41 pm
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.
😎
May 7, 2008 at 2:48 pm
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.
May 8, 2008 at 1:36 pm
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
May 8, 2008 at 1:46 pm
Hi GSquared,
Why didn't I think of that? You are a true SQL developer. Thank you for your time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply