Thursday, January 28, 2010

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.

1 comment:

  1. Heh. Yep, EXEC has three forms or "modes" and that is the least known and used. There's the direct mode: "EXEC uspMySproc " that everyone knows. Then there's dynamic execution: "EXEC (@SomeSQLcommands)". And finally there's what I call variable execution "EXEC @SprocName", which is what you've just discovered. Probably it's most significant usage is as an error, when someone is trying to use dynamic execution but forgets to use the parenthesis "( ... )". (it's also the reason why this common mistake results in the mysterious error message "Could not find stored procedure 'select from Fubar'." instead of something like "invalid syntax, etc..".)

    ReplyDelete

So what do you think I am?