Need help in case statement

Transact-SQL
1

I'm using the below statement find the whether ORG station is ours or DST station is ours. If i get ORG then it will be 1 if DST then 2 else null
SELECT CASE
WHEN STATION_CODE =@ORG THEN '1'
WHEN STATION_CODE =@DST THEN '2'
ELSE NULL END STATUS
FROM M_COUNTRY_CODE_MATRIX
WHERE (STATION_CODE =@ORG OR STATION_CODE =@DST)
AND COUNTRY_CODE ='IN'

but some times i may pass empty string to this query.. when i pass empty string it is not returning as null.. instead its not returning anything. but i want as NULL value. please help me to get this done.

Table M_COUNTRY_CODE_MATRIX data will be as below for country_code 'IN'
Station
PND
CHN
BMY
BNG
DEL

2

hi

I tried it

I hope this helps
:slight_smile:
:slight_smile:

drop create sample data 
drop table M_COUNTRY_CODE_MATRIX 

create table M_COUNTRY_CODE_MATRIX 
(
COUNTRY_CODE VARCHAR(100),
STATION_CODE VARCHAR(100) 
)
go 

INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'IN','PND'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'IN','CHN'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'IN','BMY'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'IN','BNG'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'IN','DEL'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'U.S.A','ASD'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'U.S.A','XXX'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'U.S.A','RET'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'U.S.A','AWT'
INSERT INTO M_COUNTRY_CODE_MATRIX SELECT 'U.S.A','HHH'
go 


--SELECT * FROM M_COUNTRY_CODE_MATRIX 
--GO
SQL 
DECLARE @ORG VARCHAR(100) = 'PND' 
--DECLARE @DST  VARCHAR(100) = 'BMY' 
DECLARE @DST VARCHAR(100) = '' 

SELECT CASE 
         WHEN B.station_code = '' THEN NULL 
         ELSE B.station_code 
       END 
FROM   (SELECT station_code 
        FROM   m_country_code_matrix 
        WHERE  country_code = 'IN') A 
       RIGHT JOIN (SELECT @ORG AS STATION_CODE 
                   UNION ALL 
                   SELECT @DST) B 
               ON A.station_code = B.station_code
Result

image