happyhippo83 happyhippo83 - 1 month ago 6
SQL Question

SQL Server WHERE clause default to all unless variable entered

I have a table of about 10 columns of data and I'd like to be able to make a stored procedure that pulls only the relevant data based on user-defined parameters. A simplified version of the

fact.Spend
table looks like this:

Location | Year | SpendYear
----------+--------+-----------
New York | 2015 | 25.00
New York | 2016 | 23.20
Dallas | 2015 | 29.30
Dallas | 2016 | 25.32
San Fran | 2015 | 23.33
San Fran | 2016 | 23.97


A very basic version of what I'm trying to do with this is:

CREATE PROCEDURE spPullSpendData
(@Location VARCHAR(20), @SpendYear SMALLINT)
AS
SELECT *
FROM fact.Spend
WHERE Location = @Location
AND SpendYear = @SpendYear


But I'd like for the
@SpendYear
parameter to be optional, defaulting to all years if there is no user input. I tried a few variations using subqueries, but so far nothing's worked out quite right.

Answer Source
SELECT
    *
FROM
    fact.Spend
WHERE
    (Location = @Location)
AND 
    (SpendYear = @SpendYear or @SpendYear IS NULL)