Dynamically call a stored procedure from another stored procedure

Question

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.

Answer 1

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

Answer 2

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