SQL Server Performance Forum – Threads Archive

Nested Stored Procedures

Hi I’m trying to improve the performance of a stored procedure that has nested stored procedures. Inside the main stored procedure the developer is opening a cursor and inside this cursor they call a stored procedure, however the call with the Execute command, and the procedure name is passed in a variable string type, from what I’ve read, this could make the performance to degrade, Shouldn’t be better if they just call the procedure by its name instead of using a variable??? Beside this nested stored procedure is just outputing a value to a variable by calling a select statement, Isn’t it better if I just take this select statement out and put inside the main procedure rather than call that nested sp several times??? Of course I’m thinking about changing the cursor for other kind of command but I’ve just started to work on this procedure Thank you and best Regards
Juan

---

I’m not a developer but "Shouldn’t be better if they just call the procedure by its name instead of using a variable???" I think you are right. So, wait for developers members oppinions.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

Why not take advantage of TABLE datatype in this case to get performance rather than output to a variable and calling when its required. I feel its better than using CURSOR. Refer to the Books Online for TABLE datatype and its usage. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

It may be better to keep stored procedure, but it doesn’t have to. I guess proc parameter values are different for each call and proc may recompile because of that. That can be good if you gain more from better execution plan than the you loose beacuase of proc recompilation. To know for sure you have to test.

---

Performance might improve if you replace the EXECUTE (or EXEC) with EXEC dbo.sp_executesql and passing all variables through parameters. This might improve performance of the procedure that is being called, not the current procedure that’s doing the calling – so you get "only" indirect benefits. The example from BOL is pretty clear:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500) /* Build the SQL string once.*/
SET @SQLString =
N’SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level’
SET @ParmDefinition = N’@level tinyint’
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable In case you need to get a return value, just add the OUTPUT keyword both to the parameter name in @ParmDefinition and to the parameter in the sp_executesql call.

---

]]>