SQL Server If Null Return Everything

Question

I am trying to figure out this stored procedure.

ALTER procedure [dbo].[GetTotals]

@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 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

What I want is if @Country is not null, then return records where Country = @Country, Else returns all countries. I want the same behavior for Region.

I have tried Else Is Not Null, and Else !=Null, and Else <> Null

But nothing is working.

Edit:

I figured it out. I just wasn't calling the procedure correctly. It is working. I have to call the procedure, named GetTotals like this:

GetTotals Navy, Null, Europe This would return total Navy for all of europe.
GetTotals Navy, Albania, Null This would return totals for Navy in Albania

Hogan · Accepted Answer · 2015-05-20 00:45:12Z

3

This is often done like this:

WHERE COALESCE(@Country,Country) = Country AND COALESCE(@Region,Region) = Region

answered May 20, 2015 at 0:45

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shA.t Over a year ago

Also in SQL Server ISNULL() is an alternative ;).

Gabriel Rainha · Accepted Answer · 2015-05-20 00:34:56Z

1

WHERE (Country = @Country OR @Country IS NULL) AND (Region = @Region OR @Region IS NULL)

answered May 20, 2015 at 0:34

Gabriel Rainha

1,7431 gold badge21 silver badges36 bronze badges

Collectives™ on Stack Overflow

SQL Server If Null Return Everything

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related