February 2, 2005 at 9:08 pm
A couple of questions about a stored procedure that is not acting like I think it should... (I won't print out the whole thing because of the length, unless I need to do so later.)
Question 1: When using an if...else in a stored procedure, can you have more than one else?
For example... what I have is a proc with 4 variables (one varchar and 3 int). The programmer wants to send either one of the variables (leaving the other 3 blank) or sending no variables (returning all rows in the select statement.)
if @var <> ''
Select....
else if @var2 <> ''
Select....
else if @var3 <> ''
Select....
etc with several more "else if"s...
Question 2: Is it true that using if statements in a stored procedure causes performance problems?
Thanks for the assist!
Bob
February 3, 2005 at 9:12 am
1. Yes, you can have more than one "else if" clause in an "if" statement; but you can have only one "else" clause, and an "else if" clause cannot come after an "else" clause (if there is an "else" clause). Also, remember that only one "if", "else if" or "else" clause will be executed. If the "else if @var2 <> '' " clause is executed, then that's the end of it. Any "else if" or "else" clause after that will not be executed, even if the condition is met.
2. No. Using "if" statements in a stored procedure does not cause performance problems.
February 4, 2005 at 2:58 am
Bob
Just a thought.... if it isn't doing what you expect then if it does more than one thing in each "IF" "ELSE IF" or "ELSE" put a "BEGIN" and "END" around the block of actions.
Even if it is doing only one thing I still as a matter of readability/consistency put a BEGIN and END within the clause....
Sam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply