Stored procedure / case
-
I'm working on a large stored procedure that takes two parameters. first is a record ID, and second is an action. The action indicates what fields get updated. My first thought was to handle this the way I would in VB, using a case statement and do something like this :
case @Action
when 'ABC' then
update table set field1=field1 + 1 where id=@ID
when 'DEF' then
update table set field2=field2 + 1 where id=@ID
else
--some other logic
end
however this doesn't work. Am I missing something, or will I need to do
if @Action = 'ABC' then
begin
update table set field1=field1+1 where id=@ID
end
else
begin
if @Action = 'DEF' then
begin
update table set field2=field2+1 where id=@ID
end
else
begin
--other stuff here
end
end
I know I can do the nested If statements, but given the complexity of the procedure (currently 8 possible branches) I'm thinking it would be a LOT more complicated then using a case statement. I also don't like the idea of using several different stored procedures for basically the same function that just effects different fields.
any ideas?
thanks,
Kevin
-
Well, it probably wasn't the best way to handle this, but I came up with a way that actually simplifies the process considerably. of the 8 possible branches, 6 did the same thing, but used different columns, 1 did something different, but related, and 1 was the else part of the case. I did a nested if, but I combined the 6 that were the same process together using dynamic SQL, so I ended up only have 3 branches. makes the code MUCH easier to read, and maintain.
thanks,
Kevin
-
you could also do something like this...
update table
set field1 = CASE WHEN @Action = 'ABC' THEN field1 + 1 ELSE Field1 END,
field2
= CASE WHEN @Action = 'DEF' THEN field2 + 1 ELSE Field2 END,
...
where id=@ID
-
Hi Kevin,
You've already got a solution that works for you -- I would agree that writing some dynamic sql makes sense here -- but I have another thought for you...
In your case you really don't need a nested if at all, no matter how many branches. Your cases are mutually exclusive, and the evaluation time for the comparison will be negligible (I've tested this a bit), so you can really just write it as a series of IF statements.
IF @Action = 'ABC'
-- appropriate statement here
IF @Action = 'DEF'
-- appropriate statement here
IF @Action = 'GHI'
-- appropriate statement here
-- etc...
RETURN @@ERROR
... if you really think it's overhead to have the additional comparisons evaluated unnecessarily, you could obviate the extra comparisons with multiple RETURN statements (usually considered lousy style but frankly so are nested IFs, so WTH <g>
, as below. Either way, multiple return statements or clean single-IFs, it's prolly easier to read:IF @Action = 'ABC'
BEGIN
-- your appropriate update statement here
RETURN @@ERROR
END
IF @Action = 'DEF'
BEGIN
-- your appropriate update statement here
RETURN @@ERROR
END
IF @Action = 'GHI'
BEGIN
-- your appropriate update statement here
RETURN @@ERROR
END
--- and so on ...
--- you may want to throw an error if you got this far
--- and no case was satisfied, or you could do that at the top,
--- by validating the argument values immediately.
I'm interested in your thoughts on these variants... FWIW I have been irritated by the lack of the equivalent of a procedural CASE (or switch) statement in T-SQL a bunch of times, so I'm just casting around (no pun intended <g>
to see how other people prefer to structure this type of thing.>L<
-
I like Joel's solution, it's compact and elegant ( and easy to maintain, the code is only in one place) and there isn't really going to hurt updating several columns in one row when only one needs to change. Based on what I know so far, I would say this is not a candidate for dynamic SQL because it is not neccessary.
If you insist on going the dynamic route, I would do some performance testing before making your decision. As you know, there are performance considerations when writing Dynamic SQL (and this seems like it will be frequently called). If you are going to use dynamic SQL, paramaterize it and use sp_executesql, this will give you a greater chance of being able to reuse execution plans.
Looking at your post, this might not be an issue, but it is still something to keep in mind: When you have multiple IF statements that result in executing different queries, you can end up with really poor performance. You can end up with many recompiles, or a query plan that performs great for one branch, but miserably for other branches. This is especially true the optimizer engages in parameter sniffing. When you're writing code like that, these are things you need to consider.
Good luck and let us know how you get on!
SQL guy and Houston Magician
-
After what I saw here, I stuck with my initial solution, primarily for readability. While the case statement would have worked, it would have added a bunch of code compared to the dynamic SQL (the action column was actually the field name that was being updated for most of the options.), and since one of the actions required completely different logic, I still needed the multiple Ifs. The logic has been live for over a week now with no problems. and it's not something that will ever get heavy use, so I'm happy with the outcome.
thanks everyone for all the input!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply