CREATE PROCEDURE dbo.exec_example ( @parameter VARCHAR(10) = 'employee', @ID INT = 10 ) AS SET NOCOUNT ON; DECLARE @sp_name NVARCHAR(100); IF @parameter = 'employee' BEGIN SET @sp_name = 'dbo.uspGetEmployeeManagers' END ELSE BEGIN SET @sp_name = 'dbo.uspGetManagerEmployees' END; EXEC @sp_name @id; RETURN;The interesting part is where the stored procedure name is put in a variable and then you just use Exec @sp_name with the parameters listed after. I’d never seen this done before, and my first comment on the code was, “I don’t think that should work”, but it does. I also looked it up in BOL and here’s a couple of snippets from the EXECUTE (Transact-SQL) entry:
From the Arguments section:
From the Examples section:
Is the name of a locally defined variable that represents a module name.
I had been contacted to try to help clean up and improve the code my friend was writing and this was a time where I learned something. This is one of the things I love about SQL Server, and technology in general, you can learn from anyone, even when you’ve been called in as the expert.
E. Using EXECUTE with a stored procedure variableThe following example creates a variable that represents a stored procedure name.
DECLARE @proc_name varchar(30); SET @proc_name = 'sys.sp_who'; EXEC @proc_name;