SQL IF NOT EXISTS - how to return/interpret when it does exist

Question

I have an IF NOT EXISTS statement. IF it doesn't exist an INSERT statement follows. IF EXISTS I need to understand what is/can be returned so that I can interpret it with PHP.

so:

IF NOT EXISTS (SELECT id FROM users WHERE lname='SMITH') INSERT INTO users (lname='SMITH')

I thought that the negative would return no result so could use a standard

if ($result){echo "record added";} else {echo "duplicate - no record added";}

But that's not what's happening and I'm not sure how to achieve what I need - any suggestions?

Thank you for your kind attention.

How is $result being defined? SQL is still going to generate an 'I did something' object, although I'm not entirely sure what that looks like in PHP. To my knowledge, if no row is inserted, you'll get a (fairly) standard code 100; however, I'm not sure what the way you've written the SQL is going to do to that. I'd prefer re-writing it into a form which will always execute the INSERT, but it won't always have a row to do so. — Clockwork-Muse
– Clockwork-Muse, Commented Apr 24, 2012 at 20:34

PinnyM · Accepted Answer · 2012-04-24 20:40:55Z

1

In both cases $result would return the response for a successfully run command and nothing more. INSERT doesn't yield any rows, and neither does an IF statement that doesn't have a matching condition.

You can use SELECT SCOPE_IDENTITY() if you want to retrieve the inserted ID, and then you can check if $result has returned a row.

edited Apr 24, 2012 at 20:40

answered Apr 24, 2012 at 20:32

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

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

4 Comments

jlisham Over a year ago

I tried SELECT SCOPE_IDENTITY first but unfortunately it returns a result regardless - it's just the LAST result so this turns out to always be affirmative. Thank you for the input, PinnyM!

PinnyM Over a year ago

Right, but the idea was to perform the SELECT as part of the IF NOT EXISTS body, similar to @nonnb's solution

jlisham Over a year ago

You're right, PinnyM...I can just use SELECT SCOPE_IDENTITY() before END and leave off the ELSE entirely. Better solution. Cheers!

jlisham Over a year ago

It's also necessary to rewrite the php a bit: instead of if ($result) it needs to be if (mssql_fetch_array($result)) (this was required for both solutions)

Baba · Accepted Answer · 2012-04-24 20:33:51Z

1

You might try something like:

SET NOCOUNT ON IF NOT EXISTS (SELECT id FROM users WHERE lname='SMITH') BEGIN INSERT INTO users (lname) VALUES('SMITH') SELECT 1 END ELSE SELECT 0

And check the returned scalar = 0 or 1 ?

edited Apr 24, 2012 at 20:33

Baba

95.3k29 gold badges172 silver badges222 bronze badges

answered Apr 24, 2012 at 20:31

StuartLC

108k18 gold badges225 silver badges294 bronze badges

1 Comment

jlisham Over a year ago

I've ended up using a variation of what you've proposed: ...SELECT 1 AS response END ELSE SELECT NULL AS response which works...will have to do some more research on this when I have more time. Thanks for the response, nonnb!

Collectives™ on Stack Overflow

SQL IF NOT EXISTS - how to return/interpret when it does exist

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related