Use of Coalesce in SQL Query

Coalesce : pick the first not null value from the passing values.

-- To know @ just copy & paste


declare
@TBLCountry table(CountryID int,Country varchar(20),CityID int,CityName varchar(20))

insert into @TBLCountry(CountryID,Country,CityID,CityName)
values
(101,'India',301,'Indore'),
(101,'India',302,'Bhopal'),
(101,'India',303,'Agra'),
(101,'India',304,'Noida'),

(102,'US',301,'NY'),
(102,'US',302,'LA'),
(102,'US',303,'Chicago'),
(102,'US',304,'SJ')

-- Parameter

declare @CountryID int,@CityID int

-- Choose only one at a time

set @CountryID = -1
set @CityID = -1

--set @CountryID = 101
--set @CityID = -1

--set @CountryID = 101
--set @CityID = 301

--set @CountryID = -1
--set @CityID = 301

-- Query

if (@CountryID = -1) set @CountryID = null
if (@CityID = -1) set @CityID = null

select * from @TBLCountry T
where T.CountryID = coalesce(@CountryID,T.CountryID)
and T.CityID = coalesce(@CityID,T.CityID)