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