CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlstored-procedures
Greg
Greg

Reputation: 47154

Dynamically call a stored procedure from another stored procedure

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'.

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''.

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

Upvotes: 6

Views: 18463

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

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

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

From EXECUTE:

[ { EXEC | EXECUTE } ]  
{   
  [ @return_status = ]  
  { module_name [ ;number ] | @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.

Upvotes: 0

TcKs
TcKs

Reputation: 26632

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

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135121

you need parentheses

exec (@sp_str)

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

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

it would not be Could not find stored procedure '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

Upvotes: 8

Related Questions