September 26, 2014 at 1:29 am
Hi all,
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. Can any one give an idea, how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Thanks,
September 26, 2014 at 3:51 am
If you want to do the second thing, you'll need to build that as a string and then execute the string.
DECLARE @ExecProc AS VARCHAR(75) --or something much bigger, depending
SET @ExecProc = 'EXECUTE ' + @ProcName + ' ' + @Param1 + ', ' @Param2 --etc.
EXEC @ExecProc
You could also look to sp_executesql to build this out in a better way.
However, it's all pretty sloppy. Why do you need to have a procedure build execution of other procedures like this? Seems like if you have the other procedure names and the parameters, you'd be better served, in most instance, of just calling those directly. Or, if you're in a wrapper procedure situation, you just call the procedures directly your self. Who cares if you have 5 different EXEC statements?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2014 at 4:01 am
It's also violating software design practices. A stored proc is supposed to return one thing, like a method does in your OO languages. You're asking about a procedure that, depending on parameters, can return apples, watermelons or airline tickets. It makes it harder to maintain and understand. And that's not even touching on the security problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2014 at 4:43 am
Excellent point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply