SQL query to get either matched records otherwise NULL records

Question

ColA    ColB    ColC

123     Null    Null

120     Null    Null

Null    1       2

Declare @ColA as Int = 123

--Rough draft, this doesn't work though

--I'm going to pass this above variable to SQL query..

Select *

from @TblA

Where @ColA = ColA or (@ColA <> ColA and ColA is Null)

Need a SQL statement such that

1. If there is a match on ColA then get only the matching records for ColA

2. If there is No match on ColA then get records with Null in Col A only

Answer 1

You can try a query like below:

SELECT * FROM @TblA 
WHERE @ColA=ColA  AND EXISTS(SELECT 1 FROM @TblA WHERE @ColA=ColA)
UNION 
SELECT * FROM @TblA 
WHERE ColA IS NULL AND NOT EXISTS (SELECT 1 FROM @TblA WHERE @ColA=ColA)

This is equivalent to

IF EXISTS(SELECT 1 FROM @TblA WHERE @ColA=ColA)
    SELECT * FROM @TblA WHERE @ColA=ColA 
ELSE 
    SELECT * FROM @TblA WHERE ColA IS NULL 

Based on your update below

Yes, there are 2 variables @ColA and @ColB. 1. If @ColB values match then get records for @ColA = ColA along with ColB = @ColB and also Null values for ColB if there are any but ColA has to have exact match without any mismatches or Nulls

updated query:

SELECT * FROM @TblA 
WHERE 
 @ColA=ColA  AND 
 ISNULL(ColB, @ColB)= @ColB AND 
 EXISTS(SELECT 1 FROM @TblA WHERE @ColA=ColA)
UNION 
SELECT * FROM @TblA 
WHERE 
  ColA IS NULL AND 
  ISNULL(ColB, @ColB)= @ColB AND 
  NOT EXISTS (SELECT 1 FROM @TblA WHERE @ColA=ColA)

Answer 2

I think this would work

Where isnull(ColA,@ColA) = @ColA

Test code returns 2 records, the match to 1 and the null

DECLARE @testTbl TABLE (colA INT, colB INT, colC INT)

INSERT INTO @testTbl (colA, colB, colC) VALUES  (1, 2, 3)
INSERT INTO @testTbl (colA, colB, colC) VALUES  (4, 5, 6)
INSERT INTO @testTbl (colA, colB, colC) VALUES  (null, 7, 8)

DECLARE @colA INT
SET @colA = 1

SELECT * FROM @testTbl WHERE ISNULL(colA, @colA) = @colA