UNION ALL statement not working with the where condition

Question

I have a stored procedure shown here, which accepts a table type as input parameter. The problem with the stored procedure is it's returning multiple records as many records exist in the Payment table. That is not the expected result - I just want to return 1 row for the account, if the account exists, or else return as INVALID.

Main goal of this stored procedure is to check whether rows exists in Payment table or not.

This is my stored procedure:

CREATE PROCEDURE [dbo].[CheckPolicy]    
    @SearchAccount AS dbo.PayerSwapType READONLY    
AS  
BEGIN  
    SET NOCOUNT ON;  
  
    CREATE TABLE #TempReferencIds
    (
        CorrectAccount nvarchar(20),
        PolicyStatus nvarchar(10),
    )

    INSERT INTO #TempReferencIds (CorrectAccount, PolicyStatus)
        SELECT t.CorrectAccount, 'VALID' 
        FROM [dbo].Payment a 
        JOIN @SearchAccount t ON a.PolicyNumber = t.CorrectAccount

        UNION ALL
    
        SELECT t.CorrectAccount, 'INVALID' 
        FROM [dbo].Payment a 
        RIGHT JOIN @SearchAccount t ON a.PolicyNumber = t.CorrectAccount
        WHERE a.PolicyNumber IS NULL

    SELECT CorrectAccount, PolicyStatus 
    FROM #TempReferencIds
END

CREATE TYPE dbo.PayerSwapType AS TABLE
(
     CorrectAccount VARCHAR(20) NOT NULL
);

Payment table data - it's the master table with policy number can be repetitive:

PolicyNumber           AccountNumber
--------------------------------------------
7003741019             ljksdahfkjA9992
7003723876             101100QTGRWI1499
7003723876             201100QTGRWI1499
7003741001             63e190SSSIUU0075
7003741001             60ds190SSSIUU0075
7003740987             601s90SSSIUU0075
7003740987             601s90SSSIUU0075
7003740987             601h90SSSIUU0075

Current result returned from the stored procedure:

CorrectAccount  PolicyStatus
---------------------------
7003741019      VALID
7003723876      VALID
7003723876      VALID
7003741001      VALID
7003741001      VALID
7003740987      VALID
7003740987      VALID
7003740987      VALID
213786777       INVALID

Expected result that should be returned from the stored procedure:

CorrectAccount   PolicyStatus
-----------------------------
7003741019       VALID
7003723876       VALID
7003741001       VALID
7003740987       VALID
213786777        INVALID

Input to the stored procedure:

7003741019
7003723876
7003741001
7003740987
213786777

This is what I have tried in the stored proc last statement, is this the correct way to do it?

select distinct CorrectAccount, PolicyStatus
from #TempReferencIds  

Answer 1

I think you can change your whole procedure to just:

select CorrectAccount
    , case when exists (
        select 1
        from payment p
        where p.PolicyNumber = a.CorrectAccount
    ) then 'VALID' else 'INVALID' end as PolicyStatus
from @SearchAccount a;

Another suggestion is to avoid RIGHT JOINs, they can be expressed as LEFT JOINs and usually it's much easier to read and understand, since your "main" table goes first

Answer 2

I'm not sure why you're using a UNION here at all. This looks like a single query, using a LEFT OUTER JOIN to determine existence to me.

I've simplified the example a little:

/* Mock up the table itself */ 
DECLARE @Accounts TABLE (PolicyNumber BIGINT, AccountNumber VARCHAR(20));
INSERT INTO @Accounts (PolicyNumber, AccountNumber) VALUES
(7003741019, 'ljksdahfkjA9992'), (7003723876, '101100QTGRWI1499'), (7003723876, '201100QTGRWI1499'), (7003741001, '63e190SSSIUU0075'),
(7003741001, '60ds190SSSIUU0075'), (7003740987, '601s90SSSIUU0075'), (7003740987, '601s90SSSIUU0075'), (7003740987, '601h90SSSIUU0075');

/* Mock up the input */
DECLARE @Table TABLE (PolicyNumber BIGINT);
INSERT INTO @Table (PolicyNumber) VALUES
('7003741019'), ('7003723876'), ('7003741001'), ('7003740987'), ('213786777');


SELECT t.PolicyNumber, CASE WHEN MAX(a.PolicyNumber) IS NOT NULL THEN 'Valid' ELSE 'Invalid' END
  FROM @Table t
    LEFT OUTER JOIN @Accounts a
      ON t.PolicyNumber = a.PolicyNumber
 GROUP BY t.PolicyNumber;

PolicyNumber	(No column name)
213786777	Invalid
7003723876	Valid
7003740987	Valid
7003741001	Valid
7003741019	Valid

We start with the input table, and then LEFT OUTER JOIN to the table. If the Policy Number exists it will not be null. Using this we can use a CASE expression to give us back the correct string.

I've used a GROUP BY here because you were concerned with duplicate rows. If it's not possible for the input or the table to have more than one row per Policy Number you won't need the GROUP BY or the MAX.

If there is a reason you must do this with a UNION then you should pay attention to the differences between UNION and UNION ALL. UNION ALL intentionally allows for duplicate rows to be returned. UNION will filter them down to only bring back a row from each query.