Optional Parameter or different stored procedure?

Hi I have a table Sales. Usually I will be adding to this table all data for a month. So:

CREATE Procedure AddMonth
@EnterMonth as int
WHERE Y.Month = @Month

Now sometimes I just want to only add Sales for a certain product. So two questions:

  1. Should I use a new stored procedure for sales by product and month? Thinking with regards to speed.
  2. Or, use an optional parameter of the Product. If such a thing is possible, of course.


more ▼

asked Feb 19, 2010 at 02:19 PM in Default

Diarmuid gravatar image

63 2 2 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

You could add @Product as an optional parameter, but would have to be careful of parameter sniffing causing bad plans.

That would look like (assuming product is an int)

CREATE Procedure AddMonth
@EnterMonth as int,
@product int = null
WHERE Y.Month = @Month
and (@product is null or product = @product)

Make sure you check out the varied discussions on optional parameters.

more ▼

answered Feb 19, 2010 at 02:36 PM

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

(comments are locked)
10|1200 characters needed characters left

+1 to kev - but I would write

ISNULL(@productID, productID) = productID

Instead of using an OR clause.

However it's not clear to me what you mean - because if you mean inserting all sales for a product in any month, then I woild split that out into a separate procedure...

more ▼

answered Feb 19, 2010 at 03:19 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

With such a generic question, it's hard to be specific, but in the interest of being contrarian...

No, don't use the same procedure with an optional parameter (which is possible, as Kev & Matt showed). Instead create a new procedure. The reason being, either Kev's or Matt's approach could lead to scans in the Product table and from a purely performance centered point of view, having a specified query that didn't include the ISNULL or the OR function would result in better index use and therefor more consistent and usually better performance.

more ▼

answered Feb 19, 2010 at 03:47 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 I agree with you all, but from a performance perspective you should be careful about "optional" paramters as Grant answer. Even if you don't get scans you could end up with recompiles of the procedure and depending on complexity and usage of the procedure, this could lead to a bottleneck.
Feb 19, 2010 at 07:05 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

I agree with Kev and Matt, but just for the sake of options remember you can do this:

CREATE Procedure AddMonth (@EnterMonth as int, @product int = null) AS if @product is null  INSERT INTO X  SELECT *  FROM Y  WHERE Y.Month = @EnterMonth else  INSERT INTO X  SELECT *  FROM Y  WHERE Y.Month = @EnterMonth and  product = @product 
more ▼

answered Feb 19, 2010 at 08:00 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

Thanks to everyone for their answers. Timoty, from the other answers, it seems like it would be more efficient to have a seperate procedure when I want all sales for a product in a month. Is your code a way round this, or would it still suffer from the recompile/preformance issues mentioned above? My main goal is consistent good performance from these procedures.
Feb 22, 2010 at 06:12 AM Diarmuid
I believe that this and anything with optional parameters would have the potential to run into the recompiles mentioned by Hakan, but I have not done testing to confirm or deny this, so that is only an educated guess right now.
Feb 22, 2010 at 02:15 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left


I just had to check this out. With SQL Server 2005, doing a COUNT(*) on a table with some eight million rows, non clustered PK being an int identity column.

I don't know how well it shows on this picture, but three queries, delivering the same result creates different plans, where the BETWEEN-approach "costs" 6% and the other two "costs" 46% and 47%.

![Screenshot from SSMS2005][1]


I saw this question now, very, very long after it has been answered, when Diarmuid got the Popular Question badge.

I just wanted to add something that might or might not be valid anymore. When I was at a training course with MVP Tibor Karaszi, he told me that using BETWEEN is more efficient than using WHERE (productid=@productid OR @productid is null). That was when we started using SQL Server 2000 at work, having used SQL Server 7, so I don't know if this is still valid.

CREATE Procedure AddMonth
(@EnterMonth as int,
@product int = null)
    SELECT *
    FROM Y
    WHERE Y.Month = @EnterMonth and 
        product = BETWEEN COALESCE(@product, -2147483648) AND COALESCE(@product, 2147483648)

Does anyone know if this is still valid?

[1]: http://www.hejaenkoping.se/Query3.jpg
more ▼

answered Dec 16, 2010 at 02:28 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

Wouldn't that translate to the same thing behind the scenes once the optimiser has looked at it?
Dec 16, 2010 at 02:37 AM WilliamD

@WilliamD I'm looking in SQL Server 2005 and see that the plan differs with a slightly higher estimated CPU cost with the WHERE product=@product or @product is null approach compared to the BETWEEN approach. Estimated row count differs as well (with a factor 4). So the optimiser still treats the two differently.

When comparing the approaches for a table with 10M+ rows, I get a "Paralellism (Gather Streams)" in the plan for the BETWEEN-approach, not for the other approach.
Dec 16, 2010 at 03:53 AM Magnus Ahlkvist

Also of note, is that the solution suggested by TimothyAWiseman would not be a cause of severe recompiles take a look at this from [Gail Shaw][1].

[1]: http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/
Dec 16, 2010 at 04:10 AM WilliamD
I edited my answer with a screenshot from SSMS where I test three ways of doing the same thing.
Dec 16, 2010 at 04:12 AM Magnus Ahlkvist
Is that esimated or "real" costs? You mentioned that parallelism was there for BETWEEN and not the others, but the picture shows the reverse. I think that the cost may be incorrect if the values really are nulls. I would think the plan shown for the BETWEEN would lose out to an ordered index scan. The first and last plans are basically identical, and would be more consistant in execution time (if slightly slower for a non NULL @c value).
Dec 16, 2010 at 04:19 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 19, 2010 at 02:19 PM

Seen: 7789 times

Last Updated: Apr 21, 2010 at 05:36 PM