Subscribe to this blog
Briefcase
Print
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:
@ module_name_var
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 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;
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?