Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Steve Jones on 29 January 2010

I ran across that years ago and it confused me as well. Luckily BOL bailed me out at that time as well.

Posted by Rajesh on 5 February 2010

So true: "You can learn from anyone, even when you’ve been called in as the expert."

Posted by cameron.hebert on 5 February 2010

You will see this used often in dynamic sql.

Posted by Jason Brimhall on 5 February 2010

It is frequently when you put yourself out there to help somebody that you learn something new about SQL too!

Nice writeup

Posted by RBarryYoung on 5 February 2010

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..".)

Posted by VALEK on 6 February 2010

The good thing is that the actual parameter of the outer SP does not take the called SP name, but just a decision trigger for one of the two possible.

After all it is quite normal to do what's shown.

Forgetting the actual EXEC trick, the problem in the code is the redundant variable and the ELSE area.

I would rewrite the code as this:

IF @parameter = 'employee'

BEGIN

 EXEC dbo.uspGetEmployeeManagers @id;

END

ELSEIF @parameter = 'manager'

BEGIN

EXEC dbo.uspGetManagerEmployees @id;

END

ELSE

RAISERROR ('bad parameter value',16,1)

END

Posted by Jack Corbett on 12 February 2010

Thanks for the comments.  Especially Barry for the more detailed explanantion.

Valek,

Thanks for the updated code which really is probably a better way to code it, but I was just providing an example of what I saw and how I didn't know it worked.  The only thing with the code you provided is that T-SQL does not have and ELSEIF operator.  You'd either need to nest the second IF or just have 2 IF's and the second one would have an ELSE that catches other values.

Posted by Tom.Thomson on 16 February 2010

Maybe Valek just used a very thin space between else and if?

Leave a Comment

Please register or log in to leave a comment.