CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
David.Warwick
David.Warwick

Reputation: 740

SQL Server Conditions in Where Clause

I am taking an SQL Server class. I am having trouble with a Conditional Where clause in a stored procedure. 2 of the 3 parameters could be Null.

@Service nvarchar(50),
@Country nvarchar(50)  = NULL,
@Region nvarchar(50) = NULL

as 

SELECT
  CASE @Service
    WHEN 'Army' THEN Sum(Army)
    WHEN 'Navy' THEN Sum(Navy)
    When 'Marine_Corps' then Sum(Marine_Corps)
    When 'Air_Force' then Sum(Air_Force)
                ELSE NULL
  END

  as "ServiceTotal"

FROM
  All_Records

 WHERE 

if @Country is not null @Country and if @Region !=Null @Region = Region

How can I restructure the Where clause? I am coming from an Access background.

GetTotals Navy

OR

GetTotals Navy,Albania,Europe

I would like to make the Service mandatory and Country and Region optional. So I want to be able to call the procedure like. So, if I don't specify Region and Country, it should just return all Navy.

Is this possible?

Upvotes: 2

Views: 83

Answers (2)

sqluser
sqluser

Reputation: 5672

In terms of performance it's better to not to use function in WHERE clause.

SELECT  CASE @Service
            WHEN 'Army' THEN Sum(Army)
            WHEN 'Navy' THEN Sum(Navy)
            When 'Marine_Corps' then Sum(Marine_Corps)
            When 'Air_Force' then Sum(Air_Force)
            ELSE NULL
        END AS "ServiceTotal"
FROM All_Records
WHERE Country = CASE WHEN @Country IS NOT NULL THEN @Country ELSE Country END
AND Region = CASE WHEN @Region IS NOT NULL THEN @Region ELSE Region END

Using ISNULL function

SELECT  CASE @Service
            WHEN 'Army' THEN Sum(Army)
            WHEN 'Navy' THEN Sum(Navy)
            When 'Marine_Corps' then Sum(Marine_Corps)
            When 'Air_Force' then Sum(Air_Force)
            ELSE NULL
        END AS "ServiceTotal"
FROM All_Records
WHERE Country = ISNULL(@Country ,Country)
AND Region = ISNULL(@Region ,Country)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Try this:

Where (@country is null or @country = country)
And (@region is null or @region = region)

What you are searching for is called a catch-all query.

Upvotes: 0

Related Questions