T-SQL 存储过程 NULL 输入值导致选择语句失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/380889/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Stored Procedure NULL input values cause select statement to fail
提问by George Mastros
Below is a stored procedure to check if there is a duplicate entry in the database based upon checking all the fields individually (don't ask why I should do this, it just has to be this way).
下面是一个存储过程，用于根据单独检查所有字段来检查数据库中是否有重复条目（不要问我为什么要这样做，它只是必须这样）。
It sounds perfectly straightforward but the SP fails. The problem is that some parameters passed into the SP may have a null value and therefore the sql should read "is null" rather than "= null". I have tried isnull(),case statements,coalesce() and dynamic sql with exec() and sp_executesql and failed to implement any of these. Here is the code...
这听起来非常简单，但 SP 失败了。问题是传递给 SP 的某些参数可能具有空值，因此 sql 应读取“为空”而不是“=空”。我已经尝试过 isnull()、case 语句、coalesce() 和带有 exec() 和 sp_executesql 的动态 sql，但未能实现其中任何一个。这是代码...
CREATE PROCEDURE sp_myDuplicateCheck
@userId int,
@noteType char(1),
@aCode char(3),
@bCode char(3),
@cCode char(3),
@outDuplicateFound int OUT
AS
BEGIN
SET @outDuplicateFound = (SELECT Top 1 id FROM codeTable
WHERE userId = @userId
AND noteType = @noteType
AND aCode = @aCode
AND bCode = @bCode
AND cCode = @cCode
)
-- Now set the duplicate output flag to a 1 or a 0
IF (@outDuplicateFound IS NULL) OR (@outDuplicateFound = '') OR (@outDuplicateFound = 0)
SET @outDuplicateFound = 0
ELSE
SET @outDuplicateFound = 1
END
回答by n8wrl
I think you need something like this for each possibly-null parameter:
我认为每个可能为空的参数都需要这样的东西：
AND (aCode = @aCode OR (aCode IS NULL AND @aCode IS NULL))
回答by George Mastros
If I understand your question correctly, then I encourage you to do a little research on:
如果我正确理解你的问题，那么我鼓励你做一些研究：
SET ANSI_NULLS OFF
If you use this command in your stored procedure, then you can use = NULL in your comparison. Take a look at the following example code to see how this works.
如果在存储过程中使用此命令，则可以在比较中使用 = NULL。看看下面的示例代码，看看它是如何工作的。
Declare @Temp Table(Data Int)
Insert Into @Temp Values(1)
Insert Into @Temp Values(NULL)
-- No rows from the following query
select * From @Temp Where Data = NULL
SET ANSI_NULLS OFF
-- This returns the rows where data is null
select * From @Temp Where Data = NULL
SET ANSI_NULLS ON
Whenever you SET ANSI_NULLS Off, it's a good practice to set it back to ON as soon as possible because this may affect other queries that you run later. All of the SET commands only affect the current session, but depending on your application, this could span multiple queries, which is why I suggest you turn ansi nulls back on immediately after this query.
每当您将 SET ANSI_NULLS 设为 Off 时，最好尽快将其重新设为 ON，因为这可能会影响您稍后运行的其他查询。所有 SET 命令只影响当前会话，但根据您的应用程序，这可能跨越多个查询，这就是为什么我建议您在此查询后立即重新打开 ansi nulls。
回答by Jason Stevenson
I think this should work with COALESCE function. Try this:
我认为这应该与 COALESCE 功能一起使用。尝试这个：
CREATE PROCEDURE sp_myDuplicateCheck
@userId int,
@noteType char(1),
@aCode char(3),
@bCode char(3),
@cCode char(3),
@outDuplicateFound int OUT
AS
BEGIN
SET @outDuplicateFound = (SELECT Top 1 id FROM codeTable
WHERE userId = @userId
AND noteType = @noteType
AND COALESCE(aCode,'NUL') = COALESCE(@aCode,'NUL')
AND COALESCE(bCode,'NUL') = COALESCE(@bCode,'NUL')
AND COALESCE(cCode,'NUL') = COALESCE(@cCode,'NUL')
)
-- Now set the duplicate output flag to a 1 or a 0
IF (@outDuplicateFound IS NULL) OR (@outDuplicateFound = '') OR (@outDuplicateFound = 0)
SET @outDuplicateFound = 0
ELSE
SET @outDuplicateFound = 1
END
Good Luck!
祝你好运！
Jason
杰森
回答by Eric Sabine
I would first add a check to see if all of the parameters were null at run time, i.e.,
我将首先添加一个检查以查看所有参数在运行时是否为空，即，
IF(COALESCE(@userId, @noteType, @aCode, @bCode, @cCode) IS NULL)
BEGIN
-- do something here, log, print, return, etc.
END
Then after you've validated that the user passed something in you can use something like this in your WHERE clause
然后在您验证用户传递了某些内容之后，您可以在 WHERE 子句中使用类似的内容
WHERE userId = COALESCE(@userId, userId)
AND noteType = COALESCE(@noteType, noteType)
AND aCode = COALESCE(@aCode, aCode)
AND bCode = COALESCE(@bCode, bCode)
AND cCode = COALESCE(@cCode, cCode)
EDIT: I may have missed the intent that if the parameter was passed in as null that means you explicitly want to test the column for null. My above where clause assumed that the null parameter meant 'skip the test on this column.'
编辑：我可能错过了如果参数作为 null 传入的意图，这意味着您明确想要测试该列是否为 null。我上面的 where 子句假定空参数意味着“跳过此列上的测试”。
Alternatively, I believe you can use your original query and add the ANSI_NULLS set option at the stored procedure create time. For example,
或者，我相信您可以使用原始查询并在存储过程创建时添加 ANSI_NULLS 设置选项。例如，
SET ANSI_NULLS OFF
GO
CREATE PROC sp_myDuplicateCheck....
Effectively this should allow your code to then evaluate column=null as opposed to column is null. I think Kalen Delaney once coined the ANSI_NULLS and QUOTED_IDENTIFIERoptions as 'sticky options' because if they're set at procedure create time they stay with the procedure at run time, regardless of how the connection at that time is set.
实际上，这应该允许您的代码然后评估 column=null 而不是 column is null。我认为 Kalen Delaney 曾经将 ANSI _NULLS 和 QUOTED_IDENTIFIER选项创造为“粘性选项”，因为如果它们是在过程创建时设置的，它们在运行时会留在过程中，而不管当时的连接是如何设置的。
回答by Learning
Try this :
尝试这个 ：
CREATE PROCEDURE sp_myDuplicateCheck
@userId int = 0,
@noteType char(1) = "",
@aCode char(3) = "",
@bCode char(3) = "",
@cCode char(3) = "",
@outDuplicateFound int OUT
AS
BEGIN
SET @outDuplicateFound = (SELECT Top 1 id FROM codeTable
WHERE @userId in (userId ,0)
AND @noteType in (noteType,"")
AND @aCode in (aCode , "")
AND @bCode in (bCode , "")
AND @cCode in (cCode ,"")
)
-- Now set the duplicate output flag to a 1 or a 0
IF (@outDuplicateFound IS NULL) OR (@outDuplicateFound = '') OR (@outDuplicateFound = 0)
SET @outDuplicateFound = 0
ELSE
SET @outDuplicateFound = 1
END
What this basically does is to provide default values to the input parameters in case of null and then in the where condition checks onlyif the values are not equal to the default values.
这基本上是在为空的情况下为输入参数提供默认值，然后仅在值不等于默认值时才在 where 条件中检查。
回答by Ankur Jain
SET ANSI_NULLS OFF/On
SET ANSI_NULLS OFF/On
That way you can do colName = null
这样你就可以做到 colName = null
