If Exists command in setting a variable

Question

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?

Gordon Linoff · Accepted Answer · 2017-09-15 18:48:26Z

2

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;

answered Sep 15, 2017 at 18:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Sean Lange Over a year ago

You could actually eliminate the EXISTS entirely. Just set the variable as the default value and then your select statement to populate it with the query. If no rows are returned the value will still be the default. :)

SqlZim Over a year ago

@Shawn I use select 1 out of habit. Both exists() and not exists() do not return rows, so you could use select null or select 1. Based on this article EXISTS Subqueries: SELECT 1 vs. SELECT * - Conor Cunningham using select 1 will avoid having to examine any unneeded metadata for that table during query compilation. EXISTS Subqueries: SELECT 1 vs. SELECT * - Martin Smith ran tests that show no difference in actual performance though.

SqlZim Over a year ago

@SeanLange I see you didn't read the second half of my comment. -- Martin Smith's answer goes over all of that in the second link. Also, the compilation of a query is different than the execution of a query.

Sean Lange Over a year ago

@SqlZim yes I posted my comment before reading the second article which refuted what the author of the first one stated. :)

Shawn Over a year ago

@SqlZim Sorry, "return rows" was kind of an oversimplification on my part. I meant that IF EXISTS just checks for the existence of a record. And I just tried the SELECT 1/0: very interesting. Thanks, Sean. That's one of those "confirm something you didn't know you didn't know" things. I will say, having read both of Zim's links, I would tend to trust someone who worked on the compiler, but only for the version they worked on. The 2nd article shows that it doesn't work the same way in 2011/14 as it was supposed to in 2008.

|

Hogan · Accepted Answer · 2017-09-15 21:31:28Z

1

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

edited Sep 15, 2017 at 21:31

answered Sep 15, 2017 at 18:49

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

3 Comments

Shawn Over a year ago

I like this one. That would handle a NULL PID_GUID already in PID. The question then would be if that was the intended behavior. Gordon's query will make @OLDPID = NULL if it was already NULL in PID. But if a NULL is a valid response, this would overwrite it. But yes, COALESCE() is pretty awesome. :-)

Shawn Over a year ago

@user3463443 For SQL 2008, just use SELECT TOP 1 PID_GUID and drop the FETCH FIRST 1 ROW ONLY. I think that's DB2. MS SQL has a FETCH, but it needs to be used with an OFFSET (outside of a cursor), and that only works in 2012+.

Hogan Over a year ago

@user3463443 -- yes if you are using SQL Server then use the TOP 1 syntax as Shawn suggests

Collectives™ on Stack Overflow

If Exists command in setting a variable

2 Answers 2

12 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related