检查 SELECT 是否返回存储过程中的任何行 [英] Check if SELECT Returns Any Rows in Stored Procedure

问题描述

我正在编写一个基本的 SELECT 查询，例如:

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

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

然后我想使用 SELECT 的结果来执行一个 INSERT，如下所示:

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

我的问题是，如何检查**上面的SELECT查询返回0行**?

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

推荐答案

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

...因为 IF EXISTS 会在遇到匹配的第一行后立即返回.

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

我不建议使用 @@ROWCOUNT 只是因为您每次都必须实现(并忽略)完整的结果集.

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

这篇关于检查 SELECT 是否返回存储过程中的任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！