February 1, 2002 at 5:35 pm
Hi, a have two questions.
What is better, in performance, and Why? 1. Return a value of store procedure with: a. Output parameter b. Return Value 2. Create store procedures: a. with static t-sql (the common) b. with dinamyc t-sql (using EXEC @string)
Thanks for your answer.
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
February 1, 2002 at 9:49 pm
For your first question, you can only return numeric values with the return keyword in your stored procedure. So, if you need to return something other than a number then you must use output parameters.
For your second question, I have used both extensively and find uses for sp's that are static and those that are dynamic. I have found that converting dynamic to static often performs better. I'm sure most of the performance improvement is due to cached execution plans and queries that are tuned better. I believe both have their places, however I am beginning to lean toward making most SP's static unless they can't be.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 2, 2002 at 7:49 am
Typically we use RETURN from the stored procedure to signal something unrelated to the data we're looking for. For instance, returning 0 (default) means successful completion of the procedure. However, we might want to return 10 when a user enters in a parameter incorrectly. RETURN can be thought of returning our own error codes, with the aforementioned 0 for success, any other number for abnormal completion. If we have a set standard of numbers to use with RETURN for our project to be applied across all stored procedures (for instance that 10 means the user didn't pass a last name regardless of stored procedure), they can make things pretty easy on us.
With respect to dynamic versus static queries in stored procedures, if you go with static queries, if you avoid the known recompile issues, you'll have an execution class that can be reused over and over. With dynamic SQL queries, the way SQL Server handles queries versus stored procedures with respect to the procedure cache means that a query that you build is more likely to be dropped out of the buffer than the stored procedure and thus will not be an execution plan to be reused.
Also, SQL Server has to make a second trip looking for a plan, although not as bad as the second trip after an initial SP:Cache Miss event, for the dynamic query. Still, though, there may be times when we resort to dynamic SQL queries, especially if we want to avoid stored procedure recompiles for complex code. For a little more on caching, here's an article I wrote:
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
One thing also to consider is that if a dynamic query could be parameterized, you might consider using sp_executesql. Queries that can be written to use sp_executesql with parameters have a better chance of caching an execution plan to be reused.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 2, 2002 at 7:52 pm
I agree about the use of the return code for error signaling. I doubt there is much difference in overhead if you declare true output parameters - figure no matter what you do you're going to send at least one network packet each way to get the answer back.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply