Optional Parameter or different stored procedure?

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

EDIT

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%.

END EDIT

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)
AS
INSERT INTO X
    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?