CODEWITHSUNDEEP
sqlsql-servervariablesif-statementexists
user3463443
user3463443

Reputation: 35

If Exists command in setting a variable

I've got a working IF EXISTS command to select a PID_GUID that's already in the tables, or to select a value to use as a PID_GUID if it does not exist already 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 what I would like to do is put this into setting a variable such as this;

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 would I go about doing this in SQL2008?

Upvotes: 3

Views: 71

Answers (2)

Hogan
Hogan

Reputation: 70523

I'd use COALESCE() because COALESCE() can do anything.

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 no stinking 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')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Set the variable in each statement:

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;

Upvotes: 2

Related Questions