SQL Server Conditions in Where Clause

Question

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?

sqluser · Accepted Answer · 2015-05-19 03:33:50Z

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)

Zohar Peled · Accepted Answer · 2015-05-19 03:30:26Z

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.

Collectives™ on Stack Overflow

SQL Server Conditions in Where Clause

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related