Query is returning empty records

I am trying to select 3 columns from a table which needs to satisfy the following requirements (in the UPDATE section)

        SELECT A.ACTIVITY_CD, A.REASON_CD, A.REASON_DESC      INTO #TmpViewResult      FROM CFG_REASON A, #TmpViewParam B      WHERE A.ACTIVITY_CD = CASE WHEN B.ActivityCd = '' THEN A.ACTIVITY_CD ELSE B.ActivityCd END      AND A.REASON_CD = CASE WHEN B.ReasonCd = '' THEN A.REASON_CD ELSE B.ReasonCd END  

It is returning empty records for me. What did i do wrong here and how should i change it?

UPDATE:

The answer below does not work for me. It is still giving me empty records. I have this code before the queries above.

SELECT ActivityCd, ReasonCd      INTO #TmpViewParam      FROM OPENJSON(@Param)      WITH (ActivityCd NVARCHAR(10),      ReasonCd NVARCHAR(10))  

Below is the Param passed, both param can be empty or contains value.

@Param = N'{"ACTIVITY_CD": "RS00001", "REASON_CD": ""}'  

Requirements

ActivityCd

• Blank = all activity
• With value = Specific activity code

ReasonCd

• Blank = all reason for the above selected activitycd
• With value = specific reason for the selected activity code.

Expected result with the @Param above : {"ACTIVITY_CD": "RS00001", "REASON_CD": "xxx", "REASON_DESC": "xxx"} and the rest of the records according to the param

https://stackoverflow.com/questions/66677559/query-is-returning-empty-records March 18, 2021 at 12:46AM