T-SQL存储过程NULL输入值导致select语句失败

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应该读为“is null”而不是“= null”。我已尝试使用exec()和sp_executesql的isnull(),case语句,coalesce()和动态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

6 个解决方案

#1

I think you need something like this for each possibly-null parameter:

对于每个可能为null的参数,我认为你需要这样的东西:

AND (aCode = @aCode OR (aCode IS NULL AND @aCode IS NULL))

#2

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.

每当您将ANSI_NULLS设置为Off时,最好将其尽快设置为ON,因为这可能会影响您稍后运行的其他查询。所有SET命令仅影响当前会话,但根据您的应用程序,这可能跨越多个查询,这就是为什么我建议您在此查询后立即重新启用ansi null。

#3

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

#4

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 only if the values are not equal to the default values.

这基本上做的是在null的情况下为输入参数提供默认值,然后在where条件中仅在值不等于默认值时检查。

#5

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子句假设null参数意味着'跳过此列上的测试'。

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选项。例如,

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_IDENTIFIER options 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为null。我认为Kalen Delaney曾经将ANSI_NULLS和QUOTED_IDENTIFIER选项创建为“粘性选项”,因为如果将它们设置为过程创建时间,则无论在何时设置连接,它们都会在运行时保留该过程。

#6

SET ANSI_NULLS OFF/On

SET ANSI_NULLS OFF / On

That way you can do colName = null

这样你就可以做colName = null

#1