September 8, 2009 at 3:11 am
Hi all,
Today I came across an old procedure which has a number of input parameters, approx 30 , however a number of these
are defaulted to be null if not provided, approx 10.
"@ParameterSample1 INT = NULL"
This got me thinking about is it right to default your parameters or not, and what effect this would have on SQL, with regards to parameter sniffing and the execution plan etc.
The reason I beleive this procedure has been defaulted is it is called from a number of places around the system and due to poor planning and design some of these dont pass certain values in.
I was just wondering if anyone had any views on using defaults in procedures?
September 8, 2009 at 5:11 am
As far as I know parameter sniffing uses the actual values of the parameters. If the parameters were passed with a different value then the default value, then the value that was passed will be used, so I don’t see a problem with default values in the context of parameters sniffing.
I also wouldn’t say that using a default value for a parameter is a sign for poor programming. Take even system procedure such as sp_spaceused. If you pass a table’s name, then you’ll get a report for this specific table’s space. If you won’t pass anything, then you’ll get a report about the database’s space. This procedure also has a second parameter to specify if you want to run dbcc updateusage before reporting the space usage. That parameter also has a default value and you are not forced to pass it. Do you see that as poor programming?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2009 at 5:29 am
Hi,
Thanks for the reply.
You make a good point about defaults not being indicative of poor programming. What I mean is that if you have 2 process that share a procedure and 1 process changes and the other doesnt, say to add more parameters
Then maybe it is better to stop sharing the procedure and begin to use 2 seperate procedures, 1 with out the additional parameters and 1 with. However on the flip side you could argue this removes any common functionality.
I suppose, like most things SQL it's a case of 'it depends'.
September 8, 2009 at 5:42 am
Like you I would say that it depends. Suppose there is a procedure that inserts data into a temporary table, runs some complex calculations on the table (according to the parameters that were passed) and then returns that data to the client. A new application is being build and the application is using this stored procedure, but now dev team introduces a new feature that doesn’t exist in the application that is used currently. The new feature is simply modification of the order by clause. With the new application, you can decide if you want the recordset to be sorted in ascending or descending order. Apart from that there is no change in the procedure. In this case I would add a parameter with default value that mimics the current behavior instead of writing a new procedure that is almost exactly the same as the existing one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply