CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Seb
Seb

Reputation: 977

Can you use a CASE statement to evaluate a parameter you are passing into a stored procedure?

I have a stored procedure which takes four parameters. I want to call it inside of another script, but when I do so I haven't yet worked out exactly what I want to pass it. I'm trying to do this inline for the stored procedure. So my line looks like this:

EXEC stored_procedure 
    @firstparam, 
    @secondparam, 
    CASE @thirdparam 
        WHEN null THEN 0 
        ELSE 1 
    END, 
    CASE @fourthparam 
        WHEN null THEN 0 
        ELSE 1 
    END

but I'm getting the very ambiguous error of 'incorrect syntax' near the following parts:

I just want to be able to execute a CASE statement to pass in the correct variable based on what the third and fourth params are.

Upvotes: 4

Views: 8747

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need variables. The exec statement does not evaluate expressions for arguments:

DECLARE @p3flag int = (CASE WHEN @thirdparam IS NULL THEN 0 ELSE 1 END);
DECLARE @p4flag int = (CASE WHEN @fourthparam IS NULL THEN 0 ELSE 1 END);

EXEC stored_procedure @firstparam, @secondparam, @p3flag, @p4flag;

Upvotes: 9

Related Questions