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
AS
INSERT INTO X
SELECT FROM Y
WHERE Y.Month = @Month
Now sometimes I just want to only add Sales for a certain product. So two questions:
Thanks
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
)
AS
INSERT INTO X
SELECT FROM Y
WHERE Y.Month = @Month
and (@product is null or product = @product)
Make sure you check out the varied discussions on optional parameters.
+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...
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.
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
