Check if SELECT Returns Any Rows in Stored Procedure

Question

I'm writing a basic SELECT query, something like:

SELECT id, pname, pnumber  FROM tableName  WHERE pnumber IS NOT NULL 

I'd like to then perform an INSERT by using the result of that SELECT like so:

IF {**the above SELECT query returned 0 rows**} BEGIN     INSERT INTO tableName (pname,pnumber) VALUES ('bob', '38499483') END 

My question is, how can I check for the **the above SELECT query returned 0 rows**?

Answer 1

IF NOT EXISTS (SELECT ...) BEGIN   INSERT ... END 

You could also do this, if you expect that the query might often return rows (especially a lot of rows), which may offer a better opportunity to short circuit:

IF EXISTS (SELECT ...) BEGIN   PRINT 'Do nothing.'; END ELSE BEGIN   INSERT ... END 

...since IF EXISTS will return immediately after it hits the very first row that matches.

I don't recommend using @@ROWCOUNT only because you will have to materialize (and ignore) the full result set every time.

Answer 2

In MySQL you can check the number of rows returned from last SELECT query like this:

SELECT FOUND_ROWS();