Dynamically call a stored procedure from another stored procedure

Posted by Greg on Stack Overflow See other posts from Stack Overflow or by Greg
Published on 2010-05-31T17:54:00Z Indexed on 2010/05/31 18:03 UTC
Read the original article Hit count: 352

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server