I'm trying to create a query that executes a stored procedure as one part of a CASE expression that has a mix of handlers (i.e., the CASE expression needs to handle multiple inputs). The stored procedure works on its own, but when I put it into the CASE expression, it fails. What's happening?
You can't execute a stored procedure from a CASE expression. A CASE expression evaluates a list of conditions and returns the result that meets the criteria. However, you can use a CASE expression to evaluate the handlers' values and return a character string that points to the stored procedure you want to execute, as the code in Listing 1 shows. Then, you can pass the character string to an EXECUTE command that dynamically executes the stored procedure.
In the example code that Listing 1 shows, the objective is to examine the value of @state at runtime. The code then executes the appropriate stored procedure, which is related to the particular state value.