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?

score 2 · Accepted Answer · answered Sep 15 '17 at 18:48

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 '17 at 18:48

Gordon Linoff

1,242,037
58
646
786

In the `IF EXISTS`, can the `SELECT PID_GUID` be refactored to just `SELECT 1`? The `IF EXISTS` won't check if `PID_GUID` is NULL if a row is returned, will it? – Shawn Sep 15 '17 at 19:04
@Shawn It will not matter if `PID_GUID` is `null`, only if that row exists – SqlZim Sep 15 '17 at 19:13
Thanks, that's what I thought. That's kind of a micro-optimization, but I used to work with a system where we eeked out every ounce of performance we could. I became somewhat cognizant of `IF EXISTS`. As far as I know, selecting a for real column causes the SQL engine to parse the columns, whereas a 1 just returns a Boolean. – Shawn Sep 15 '17 at 19:17
due to the table's key constraints the PID_GUID could never be NULL – user3463443 Sep 15 '17 at 19:18
"never" is a scary word in SQL. :-) – Shawn Sep 15 '17 at 19:21
1

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. :) – Sean Lange Sep 15 '17 at 19:21
1

@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](http://www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/) 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](https://stackoverflow.com/a/6140367/2333499) ran tests that show no difference in actual performance though. – SqlZim Sep 15 '17 at 19:22
@SqlZim I disagree with the author about select * reading metadata. It does not care what you put in the columns, it only checks for the existence of rows. You can actually use IF EXISTS(select 1/0 from my table) and it will run just fine. – Sean Lange Sep 15 '17 at 19:24
This worked flawlessly, I used your second suggestion. now I get the PID_GUID that is already there, or I get the one I put in if it's not already there. – user3463443 Sep 15 '17 at 19:26
1

@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. – SqlZim Sep 15 '17 at 19:26
1

@SqlZim yes I posted my comment before reading the second article which refuted what the author of the first one stated. :) – Sean Lange Sep 15 '17 at 19:47
1

@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. – Shawn Sep 15 '17 at 20:07

Hogan · Answer 2 · 2017-09-15T21:31:28.277

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 '17 at 21:31

answered Sep 15 '17 at 18:49

Hogan

69,564
10
76
117

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 Sep 15 '17 at 19:09
@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+. – Shawn Sep 15 '17 at 21:09
@user3463443 -- yes if you are using SQL Server then use the `TOP 1` syntax as Shawn suggests – Hogan Sep 15 '17 at 21:30

If Exists command in setting a variable

2 Answers2