How can I execute a stored procedure whose name is in a variable?

Question

Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name varchar(512);
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I execute the stored procedure (without passing in any arguments)?

Answer 1

You can use the variable directly:

exec @stored_procedure_name;

Please see the BOL Reference on EXECUTE.

Answer 2

A procedure name is an identifier name in SQL Server, and it follows the rules of the database identifiers. For object names I prefer to use the special data type sysname (a synonym for NVARCHAR(128)).

In order to execute it you'd need to use dynamic SQL using EXECUTE or system procedure sp_executesql.

PS: see @ThomasStringer's example on an execution.

PS2: if later you'll need to add parameters, the best way to do that is to execute with sp_executesql.