从另一个存储过程动态调用存储过程

I want to be able to pass in the name of a stored procedure as a string into another stored procedure and have it called with dynamic parameters. I'm getting an error though.

我希望能够将存储过程的名称作为字符串传递到另一个存储过程，并使用动态参数调用它。我收到了一个错误。

Specifically I've tried:

具体来说我试过了：

create procedure test @var1 varchar(255), @var2 varchar(255) as
    select 1

create procedure call_it @proc_name varchar(255)
    as
    declare @sp_str varchar(255)
    set @sp_str = @proc_name + ' ''a'',''b'''
    print @sp_str
    exec @sp_str

exec call_it 'test'

So procedure call_it should call procedure test with arguments 'a', and 'b'.

因此，过程call_it应该使用参数'a'和'b'调用过程测试。

When I run the above code I get:

当我运行上面的代码时，我得到：

Msg 2812, Level 16, State 62, Procedure call_it, Line 6 Could not find stored procedure 'test 'a','b''.

消息2812，级别16，状态62，过程call_it，第6行无法找到存储过程'test'a'，'b''。

However, running test 'a','b' works fine.

但是，运行测试'a'，'b'工作正常。

3 个解决方案

#1

you need parentheses

你需要括号

exec (@sp_str)

if the proc didn't exists the message would be this

如果proc不存在，那么消息就是这个

Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'test'.

Msg 2812，Level 16，State 62，Line 1无法找到存储过程'test'。

it would not be Could not find stored procedure 'test 'a','b''

它不会找不到存储过程'test'a'，'b''

Although still a bad idea with SQL injection, try using sp_executeSQL and use parameters, see here about query plan reuse: Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly

虽然使用SQL注入仍然是一个坏主意，但尝试使用sp_executeSQL并使用参数，请参阅此处有关查询计划重用：如果未正确使用参数，则将exec更改为sp_executesql不会带来任何好处

#2

You should use the "sp_executesql" procedure. Look at MSDN - sp_executesql.

您应该使用“sp_executesql”过程。看看MSDN - sp_executesql。

#3

You don't need any kind of dynamic SQL (EXEC() or sp_executesql) at all to achieve this result:

您根本不需要任何类型的动态SQL（EXEC（）或sp_executesql）来实现此结果：

create procedure test @var1 varchar(255), @var2 varchar(255) 
as
BEGIN
    select @var1, @var2;
END;

create procedure call_it @proc_name varchar(255)
as
BEGIN
declare @param1 VARCHAR(255) = 'a'
       ,@param2 VARCHAR(255) = 'b';
exec @proc_name @param1, @param2;  --it isn't dynamic-SQL(simple procedure call)
END;

exec call_it @proc_name = 'test';

DBFiddle Demo

DBFiddle演示

From EXECUTE:

从EXECUTE：

[ { EXEC | EXECUTE } ]  
{   
  [ @return_status = ]  
  { module_name [ ;number ] | @module_name_var }   
@module_name_var

@module_name_var

Is the name of a locally defined variable that represents a module name.

是表示模块名称的本地定义变量的名称。

This can be a variable that holds the name of a natively compiled, scalar user-defined function.

这可以是一个变量，它包含本机编译的标量用户定义函数的名称。

#1