What the Exec?

I’ve been working with SQL Server for 10 years and have worked in production with versions 6.5 – 2005 and used 2008 personally.  I know I haven’t seen everything, but a couple of weeks ago a friend of mine asked me to look at some stored procedures he was working on and I saw something I’d never seen before.  The code was something like this (I’m using the AdventureWorks database, 2005 version):

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:

@ module_name_var

Is the name of a locally defined variable that represents a module name.

From the Examples section:

E. Using EXECUTE with a stored procedure variable

The 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;

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.