If the Exists command in the variable setting

Question

I have an IF EXISTS work command to select the PID_GUID that already exists in the tables, or to select a value to use as PID_GUID if it does not already exist in the tables. The command looks like this:

IF EXISTS (SELECT PID_GUID FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595')
 BEGIN
   SELECT PID_GUID FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'
 End
 ELSE
   SELECT 'a70600f4-1cff-4284-a2ce-5eb19f47cf19'

Now I would like to do this to set such a variable:

Daclare @OLDPID = VARCHAR(36)
SET @OLDPID = IF EXISTS (SELECT PID_GUID FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595')
 BEGIN
   SELECT PID_GUID FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'
 End
 ELSE
   SELECT 'a70600f4-1cff-4284-a2ce-5eb19f47cf19'

How do I do this in SQL2008?

Answer 1

Set a variable in each expression:

IF EXISTS (SELECT PID_GUID FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595')
 BEGIN
   SELECT @OLDPID = PID_GUID
   FROM PID
   WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'
 End
 ELSE
   SELECT @OLDPID = 'a70600f4-1cff-4284-a2ce-5eb19f47cf19';

Actually, I would be more inclined to use:

DECLARE @OLDPID VARCHAR(36) = 'a70600f4-1cff-4284-a2ce-5eb19f47cf19';
IF EXISTS (SELECT PID_GUID
           FROM PID
           WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'
          )
 BEGIN
     SELECT @OLDPID = PID_GUID
     FROM PID
     WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595';
 END;

Answer 2

I would use COALESCE()it because it COALESCE()can do something.

SELECT @OLDPID = COALESCE((SELECT PID_GUID 
                          FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'
                          FETCH FIRST 1 ROW ONLY),
                          'a70600f4-1cff-4284-a2ce-5eb19f47cf19')

We don't need any smelly IF statements!

SQL Server -

SELECT @OLDPID = COALESCE((SELECT TOP 1 PID_GUID 
                          FROM PID WHERE EDI_ID = '12874' OR PID = 'ROBERT' OR PID = 'R595'),
                          'a70600f4-1cff-4284-a2ce-5eb19f47cf19')