为什么这个查询在存储过程中返回不同的结果? [英] Why does this query return different results when in a stored procedure?
问题描述
我有一个带有 WHERE
子句的查询,该子句包含一个条件,该条件根据可能为 NULL 的参数检查 NULLable 列,如下所示:
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:
SELECT ...
FROM Table
WHERE NullableColumn = @NullableParameter
根据我的理解,SQL Server 应该总是将 NULL = NULL
non-NULL = NULL
评估为 false,这意味着如果 @NullableParameter
为 NULL 则上述查询将返回零条记录.
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.
这是我在查询窗口中运行查询时看到的行为,但是当我在存储过程中使用相同的查询并且 @NullableParameter
为 NULL 时,它会返回 所在的所有记录NullableColumn
为 NULL.简而言之,sproc 似乎在说 NULL = 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.
这就是问题/问题简而言之.实际的 WHERE
子句涉及更多,在下面的部分中提供了更多详细信息.
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.
我有一个数据库,其中包含一个名为 StudyResults 的表.每条记录都由 StudyResultId
主键字段唯一标识.还有一个 ParticipantId
字段,指示参与研究的受试者,以及 GroupId
,标识受试者所属的组(如果有).如果研究是单人研究,GroupId
为 NULL.ParticipantId
不能为 NULL.
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 表中的一些记录,但问题是——如果研究是单人研究,那么我只需要更新它一排;如果是小组研究,我想为该小组更新StudyResults 中针对该主题的所有行.
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.
这并不难实现.存储过程被传递一个 StudyResultId
,然后运行以下 T-SQL 来确定该行的 GroupId
和 ParticipantId
值:
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:
DECLARE @GroupId INT, @ParticipantId INT
SELECT @GroupId = GroupId,
@ParticipantId = ParticipantId
FROM StudyResults
WHERE StudyResult = @StudyResultId
接下来,我创建一个 CURSOR 来枚举感兴趣的 StudyResults 记录.注意 WHERE
子句,它说,给我记录,其中 StudyResultId
等于传递到 sproc 的 @StudyResultId
或 GroupId
和 ParticipantId
值与 StudyResults 的 GroupId
和 ParticipantId
值对齐 兴趣记录.
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.
DECLARE resultsToEnumerate CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT StudyResultId
FROM StudyResults
WHERE StudyResult = @StudyResultId OR (GroupId = @GroupId AND ParticipantId= @ParticipantId)
如果 @GroupId
为 NULL,那么比较 GroupId = @GroupId
应该总是为假,对吧?因为对于 SQL Server,NULL = NULL
为假,non-NULL = NULL
为假.
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.
但这就是奇怪的地方 - 如果我从查询窗口运行上述语句并使用 @StudyResultId
进行单人研究,则 CURSOR 包含我期望的内容 - 即单个记录.但是,如果我将完全相同的代码放在存储过程中并运行它,则 CURSOR 包含该参与者的所有单人研究!就好像它在说 @GroupId
为 NULL,所以我将返回 GroupId
为 NULL 的所有记录,但为什么呢?NULL = NULL
不应该返回记录,对吗?
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?
事实上,如果我进入 sproc 并更改 WHERE
子句并将 GroupID = @GroupID
替换为 NULL = NULL
我看到相同的结果 - 所有针对相关参与者的单人研究.所以它显然是在 sproc 中将 NULL = NULL
评估为 true(或忽略它).
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).
我可以修复"在存储过程中通过在括号中添加额外的检查来确保 GroupId IS NOT NULL
像这样:
I can "fix" this in the stored procedure by adding an additional check in the parentheses to ensure GroupId IS NOT NULL
like so:
WHERE ActivityID = @ActivityID OR (GroupID IS NOT NULL AND GroupID = @GroupID AND PatientID = @PatientID)
这就是我所做的,但我很困惑为什么 WHERE
子句在我的存储过程中的计算方式不同.
This is what I have done, but I am perplexed as to why the WHERE
clause is being evaluated differently in my stored procedure.
推荐答案
在将 ANSI_NULLS 设置为 ON 后尝试更改会话中的存储过程.
Try altering the stored procedure in a session after setting ANSI_NULLS to ON.
SET ANSI_NULLS ON
GO
alter procedure procedureName
...
来自文档:
对于存储过程,SQL Server 使用SET ANSI_NULLS 设置值来自的初始创建时间存储过程.每当存储程序随后被执行,SET ANSI_NULLS 的设置是恢复到原来的使用价值并生效.在内部调用时一个存储过程,SET的设置ANSI_NULLS 未更改.
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.
这篇关于为什么这个查询在存储过程中返回不同的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!