检查SELECT是否返回存储过程中的任何行

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

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

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**?

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

4 个解决方案

#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.

…因为IF存在会在它到达匹配的第一行之后立即返回。

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

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

#2

In MySQL to check no. of rows returned by last select query

在MySQL中检查no。最后一个select查询返回的行。

select FOUND_ROWS();

#3

you can use @@ROWCOUNT

您可以使用@@ROWCOUNT

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

after this select ask for @@ROWCOUNT

在此选择之后，请求@@ROWCOUNT

IF @@ROWCOUNT = 0
BEGIN
  INSERT ...
END

In this way you can return some data and check if that had results or no

通过这种方式，您可以返回一些数据并检查它是否有结果

#4

I have seen people have design pattern issues with this logic.

我看到人们对这种逻辑有设计模式的问题。

1 - Test existence of record.
2 - If it does not exist, insert the record.

1 -测试记录的存在性。2 -如果不存在，插入记录。

Especially if concurrency comes into play. Depending upon isolation level, you might have duplicate data or key violations.

尤其是当并发性起作用时。根据隔离级别的不同，您可能有重复的数据或违反密钥。

Why not place a primary key on pname and pnumber in the first place. I am assuming you are talking about a person table.

为什么不首先在pname和pnumber上放置一个主键呢?我猜你是在说一个人桌。

My example.

我的例子。

--
-- Setup sample table w/data
--

-- Sample table
create table #person
( 
    person_id int identity (1, 1),
    person_name varchar(64) not null,
    person_no varchar(16) not null
);
go

-- primary key
alter table #person 
   add primary key (person_no, person_name)
go

-- first insert works
insert into #person (person_name, person_no) values ('bilbo', 123)
go

The key to the solution is to trap the primary key violation. Ignoring this error might be a good or bad depending upon your business logic.

解决方案的关键是捕获主键违犯。根据业务逻辑，忽略这个错误可能是好是坏。

I am deciding to ignore the issue.

我决定不理会这个问题。

-- 
-- Ignore primary key violations
--

-- Try these steps
BEGIN TRY

    -- Second insert fails
    insert into #person (person_name, person_no) values ('bilbo', 123)

END TRY

-- Error Handler
BEGIN CATCH

    -- Ignore PK error
    IF ERROR_NUMBER() <> 2627
    SELECT
        ERROR_NUMBER() AS ErrorNumber
       ,ERROR_SEVERITY() AS ErrorSeverity
       ,ERROR_STATE() AS ErrorState
       ,ERROR_PROCEDURE() AS ErrorProcedure
       ,ERROR_LINE() AS ErrorLine
       ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

This solution eliminates duplicate entries and does not report PK violations.

这个解决方案消除了重复的条目，并且不报告PK违规。

#1

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