我们可以使用CASE和EXEC

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

我想根据用户输入选择存储的proc来执行。就像是 -

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完成，还是应该考虑使用If结构？

4 个解决方案

#1

You want to use the IF construct here:

您想在此处使用IF构造：

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

#2

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

在这种情况下，我认为即使SQL Server允许，IF也会更清楚。

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

#3

You can do it like this:

你可以这样做：

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

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

#4

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

你可以使用CASE，但你必须使用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)

#1