Run a SP within CASE statement

[Danster]

Gday all,

I am writing a stored procedure which gets a value from my database & pops it into a variable @MyVariable (varchar(20).

I need to run different stored procs based on the value of @MyVariable. The stored procs need values passed thru to them which I already have set further up in my stored proc.

I do
SELECT
CASE @MyVariable
WHEN 'value1' THEN exec SP1 @Var1, @Var2, @Var3
WHEN 'value2' THEN exec SP2 @Var1, @Var2, @Var3
WHEN 'value3' THEN exec SP3 @Var1, @Var2, @Var3
END

but the syntax check doesn't like it. Where am I going wrong?
This checks fine...
SELECT
CASE @MyVariable
WHEN 'value1' THEN 'aaaaa'
WHEN 'value2' THEN 'bbbbb'
WHEN 'value3' THEN 'ccccc'
END


cheers

Danster

 

[amrita418]

Use an If - Else instead.
Sample Code:

If @MyVariable = 'Value1' 
     exec SP1 @Var1 = 2, @Var2 = 3, @Var3 = 5
ELSE 
BEGIN
     IF @MyVariable = 'Value2'
     exec SP1 @Var1 = 1, @Var2 = 4, @Var3 = 7
     ELSE
     exec SP1 @Var1 = 14, @Var2 = 2, @Var3 = 5
END

Regards,
AA