CASE to set a variable value in SQL Server

How to use CASE to set a variable value in SQL Server?

To set a variable value in SQL server based on true / false result, CASE statements can be used.

 DECLARE @pdId int
SET @pdId = 31

DECLARE @isExists varchar(55)
SET @isExists =
       CASE
              WHEN EXISTS (SELECT PersonalDetailsId FROM Accounts
                                          WHERE PersonalDetailsId = @pdId)
                                   THEN 'Accounts detials exists.'
              WHEN EXISTS (SELECT PersonalDetailsId FROM PersonalDetails
                                          WHERE PersonalDetailsId = @pdId)
                                   THEN 'Personal Details Exists but Accounts
                                         Details do not'
              ELSE
                     'N/A'
       END;
print @isExists

In above case, the value of @pdId is 31 that exists in the Accounts table and the value of the @isExists variable is set to ‘Accounts details exists’.

When the value of @pdId is 44 that doesn’t exists in the Accounts table but it exists in PersonalDetails table, the value of @isExists is set to ‘Personal Details Exists but Accounts Details do not‘.