February 3, 2005 at 4:24 pm
Greetings to the group...
In a stored procedure, I need to be able to run various sets of code based on whether variable1 is passed or variable2 is passed or variable3 is passed or if variable4 is passed (more than one variable will not be passed at one time.) Sample below...
I'm having trouble using if...then because (best I can determine) you can only have two or at the most three options.
Thanks in advance for any assistance!
Bob
if @variable1 <> '' and @variable2 = '' and @variable3 = '' and variable4 = ''
do code #1
if @variable1 = '' and @variable2 <> '' and @variable3 = '' and variable4 = ''
do code #2
if @variable1 = '' and @variable2 = '' and @variable3 <> '' and variable4 = ''
do code #3
if @variable1 = '' and @variable2 = '' and @variable3 = '' and variable4 <> ''
do code #4
else
do code #5
February 3, 2005 at 4:46 pm
Nah, you can have as many as you need. Although it does get hard to read after a while.
If you really are dealing with varchars, and if you are only passing one variable at a time, what about:
DECLARE @LenV1 int, @LenV2 int, @LenV3 int, @LenV4 int
SELECT @LenV1 = LEN(@V1), @LenV2 = LEN(@V2), @LenV3 = LEN(@V3), @LenV4 = LEN(@V4)
IF (@LenV1 > 0)
EXEC dbo.do_code_1
ELSE IF (@LenV2 > 0)
EXEC dbo.do_code_2
ELSE IF (@LenV3 > 0)
EXEC dbo.do_code_3
ELSE IF (@LenV4 > 0)
EXEC dbo.do_code_4
else
EXEC dbo.do_code_5
February 3, 2005 at 4:50 pm
>>I'm having trouble using if...then because (best I can determine) you can only have two or at the most three options.
The code you published is obviously pseudo-code. Are you perhaps running into issues because "do code #1" actually resolves to more than 1 statement, and you're forgetting to add the BEGIN/END statements to each IF block ?
February 3, 2005 at 5:26 pm
Would this same logic work with one of the variables being varchar and the other three being int?
February 3, 2005 at 6:08 pm
Like so?
DECLARE @V1 int, @V2 int, @V3 int, @V4 varchar(10)
IF (@V1 <> 0)
EXEC dbo.do_code_1
ELSE IF (@V2 <> 0)
EXEC dbo.do_code_2
ELSE IF (@V3 <> 0)
EXEC dbo.do_code_3
ELSE IF (LEN(@V4) > 0)
EXEC dbo.do_code_4
else
EXEC dbo.do_code_5
February 3, 2005 at 6:48 pm
You got it, Dave!
Thanks very much...
February 4, 2005 at 2:35 am
>> In a stored procedure, I need to be able to run various sets of code based on whether variable1 is passed or variable2 is passed or variable3 is passed or if variable4 is passed (more than one variable will not be passed at one time.)
Sounds to me like you should be looking at four different stored procedures...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply