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

Question

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:

• First CASE
• First 1
• First , (The , just after the first CASE's END, and before the second CASE)
• Second 1

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.

Answer 1

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;