为什么此查询在存储过程中返回不同的结果？

Synopsis

I've got a query with a WHERE clause that contains a condition that checks a NULLable column against a parameter that may be NULL, like so:

我有一个WHERE子句的查询,该子句包含一个条件,该条件针对可能为NULL的参数检查NULLable列,如下所示:

SELECT ...
FROM Table
WHERE NullableColumn = @NullableParameter

From my understanding, SQL Server is supposed to evaluate NULL = NULL non-NULL = NULL as false always, meaning if @NullableParameter is NULL then the above query would return zero records.

根据我的理解,SQL Server应该将NULL = NULL non-NULL = NULL评估为始终为false,这意味着如果@NullableParameter为NULL,则上述查询将返回零记录。

That's the behavior I see when running the query in a query window, but when I use the same query in a stored procedure and when @NullableParameter is NULL it is returning all records where NullableColumn is NULL. In short, it seems like the sproc is saying NULL = NULL is true.

这是我在查询窗口中运行查询时看到的行为,但是当我在存储过程中使用相同的查询并且@NullableParameter为NULL时,它返回NullableColumn为NULL的所有记录。简而言之,似乎sproc说NULL = NULL是真的。

That's the problem/question is a nutshell. The actual WHERE clause is a bit more involved and is presented with more detail in the section below.

这就是问题/问题。实际的WHERE子句涉及更多,并在下面的部分中提供了更多细节。

The Gritty Details

I have a database with a table called StudyResults. Each record is uniquely identified by a StudyResultId primary key field. There is also a ParticipantId field, that indicates the subject involved in the study, and GroupId, which identifies what group the subject belonged to, if any. If the study was a single-person study, GroupId is NULL. ParticipantId cannot be NULL.

我有一个名为StudyResults的数据库。每条记录由StudyResultId主键字段唯一标识。还有一个ParticipantId字段,用于指示研究中涉及的主题,以及GroupId,用于标识主题属于哪个组(如果有)。如果该研究是单人研究,则GroupId为NULL。 ParticipantId不能为NULL。

I have a stored procedure that needs to update some records in the StudyResults table for a particular study, but here's the rub - if the study is single-person study then I need to just update that one row; if it was a group study I want to update all rows in StudyResults for that group, for that subject.

我有一个存储过程需要更新StudyResults表中某些特定研究的记录,但是这里有一点 - 如果研究是单人研究,那么我需要更新那一行;如果是小组研究,我想更新该组的StudyResults中的所有行。

This isn't too hard to accomplish. The stored procedure is passed a StudyResultId and then runs the following T-SQL to determine the GroupId and ParticipantId values for that row:

这不是很难实现。存储过程传递给StudyResultId,然后运行以下T-SQL以确定该行的GroupId和ParticipantId值:

DECLARE @GroupId INT, @ParticipantId INT
SELECT @GroupId = GroupId,
       @ParticipantId = ParticipantId
FROM StudyResults
WHERE StudyResult = @StudyResultId

Next, I create a CURSOR to enumerate the StudyResults records of interest. Note the WHERE clause, which says, "Give me the records where StudyResultId equals the @StudyResultId passed into the sproc or the GroupId and ParticipantId values line up with the GroupId and ParticipantId values for the StudyResults record of interest.

接下来,我创建一个CURSOR来枚举感兴趣的StudyResults记录。注意WHERE子句,它说:“给我记录,其中StudyResultId等于传递给sproc的@StudyResultId,或者GroupId和ParticipantId值与感兴趣的StudyResults记录的GroupId和ParticipantId值对齐。

DECLARE resultsToEnumerate CURSOR LOCAL FAST_FORWARD FOR 
SELECT DISTINCT StudyResultId
FROM StudyResults
WHERE StudyResult = @StudyResultId OR (GroupId = @GroupId AND ParticipantId= @ParticipantId)

If @GroupId is NULL, then comparing GroupId = @GroupId should always be false, right? Because for SQL Server, NULL = NULL is false, and non-NULL = NULL is false.

如果@GroupId为NULL,那么比较GroupId = @GroupId应始终为false,对吧?因为对于SQL Server,NULL = NULL为false,非NULL = NULL为false。

But here's what it gets weird - if I run the above statements from a query window and use a @StudyResultId for a single-person study the CURSOR contains what I expect - namely, a single record. However, if I put the exact same code in a stored procedure and run it the CURSOR contains all of the single-person studies for that participant! It's as if it is saying @GroupId is NULL, so I'll return all records where GroupId is NULL, but why? NULL = NULL should never return a record, right?

但是这就是奇怪的 - 如果我从查询窗口运行上述语句并使用@StudyResultId进行单人研究,则CURSOR包含我所期望的 - 即单个记录。但是,如果我将完全相同的代码放在存储过程中并运行它,则CURSOR包含该参与者的所有单人研究!好像它说@GroupId是NULL,所以我将返回GroupId为NULL的所有记录,但为什么呢? NULL = NULL永远不应该返回记录,对吗?

In fact, if I go into the sproc and change the WHERE clause and replace GroupID = @GroupID with NULL = NULL I see the same results - all single-person studies for the participant in question. So it clearly is evaluating NULL = NULL to true in the sproc (or ignoring it).

事实上,如果我进入sproc并更改WHERE子句并用NULL = NULL替换GroupID = @GroupID,我会看到相同的结果 - 所有参与者的单人研究。所以它显然是在sproc中将NULL = NULL评估为true(或忽略它)。

The Workaround

I can "fix" this in the stored procedure by adding an additional check in the parentheses to ensure GroupId IS NOT NULL like so:

我可以通过在括号中添加一个额外的检查来“修复”存储过程中的这一点,以确保GroupId IS NOT NULL,如下所示:

WHERE ActivityID = @ActivityID OR (GroupID IS NOT NULL AND GroupID = @GroupID AND PatientID = @PatientID)

This is what I have done, but I am perplexed as to why the WHERE clause is being evaluated differently in my stored procedure.

这就是我所做的,但我很困惑为什么WHERE子句在我的存储过程中被不同地评估。

1 个解决方案

#1

Try altering the stored procedure in a session after setting ANSI_NULLS to ON.

将ANSI_NULLS设置为ON后,尝试更改会话中的存储过程。

SET ANSI_NULLS ON
GO

alter procedure procedureName 
...

From the docs:

来自文档:

For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

对于存储过程,SQL Server使用存储过程的初始创建时间的SET ANSI_NULLS设置值。每当随后执行存储过程时,SET ANSI_NULLS的设置将恢复为其最初使用的值并生效。在存储过程中调用时,SET ANSI_NULLS的设置不会更改。