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?

Answer 1

You want to use the IF construct here:

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

Answer 2

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