can we use CASE with EXEC

Question

I want to select a stored proc to execute based on user input. Something like -

EXEC
CASE @InputParam 
  WHEN 'XML' THEN GetXMLData @ID, 'y'
  WHEN 'TABLE' THEN GetTableData @ID, 'y'
END

Can this be done with CASE or should I consider using the If construct?

`CASE` is used for inline evaluation within a query. You want `IF` since it's a flow control construct. — JNK, Apr 13 '12 at 15:35

score 13 · Accepted Answer · answered Apr 13 '12 at 15:34

13

You want to use the IF construct here:

IF @InputParam = 'XML'
    EXEC GetXMLData @ID, 'y'
IF @InputParam = 'TABLE'
    EXEC GetTableData @ID, 'y'

answered Apr 13 '12 at 15:34

JNK

63,321
15
122
138

Jeremy Pridemore · Answer 2 · 2012-04-13T16:12:38.140

3

In this scenario I think that even if SQL Server allowed that, an IF would be more clear.

IF @InputParam = 'XML'
BEGIN
    exec GetXMLData @ID, 'y'
END
ELSE IF @InputParam = 'TABLE'
BEGIN
    exec GetTableData @ID, 'y'
END

edited Apr 13 '12 at 16:12

answered Apr 13 '12 at 15:29

Jeremy Pridemore

1,995
1
14
24

aF. · Answer 3 · 2012-04-13T15:58:00.453

1

You can do it like this:

IF @InputParam = 'XML'
BEGIN
   EXEC GetXMLData @ID, 'y'
END

IF @InputParam = 'TABLE'
BEGIN
   EXEC GetTableData @ID, 'y'
END

edited Apr 13 '12 at 15:58

answered Apr 13 '12 at 15:29

aF.

64,980
43
135
198

I think you mean to use `IF` instead of `CASE` here...This is invalid syntax. – JNK Apr 13 '12 at 15:33
Yes @JNK is right, I had tried the above code earlier, and it had not worked. – neuDev33 Apr 13 '12 at 15:39
@neuDev33 ok, you may use `IF` then. I was using Sybase-ASE syntax :P – aF. Apr 13 '12 at 15:58

score 1 · Answer 4 · answered Oct 16 '17 at 18:16

You could use CASE, but you'll have to use EXEC (cmd):

DECLARE 
@cmd VARCHAR(200)
, @InputParam VARCHAR(5) ='TABLE'
, @ID INT =1

SELECT @cmd = ( CASE @InputParam 
WHEN 'XML' THEN 'GetXMLData '
      +CONVERT(VARCHAR,@ID)+', '+CHAR(39)+'y'+CHAR(39)
WHEN 'TABLE' THEN 'GetTableData '
      +CONVERT(VARCHAR,@ID)+', '+CHAR(39)+'y'+CHAR(39)
END)
EXEC(@cmd)

can we use CASE with EXEC

4 Answers4