T-SQL Stored Procedure NULL input values cause select statement to fail

Question

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

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

Solution 1

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

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

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

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.

Solution 3

I think this should work with COALESCE function. Try this:

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

Comments

[Jason Stevenson]

This should work whether your input values are NULL, or if your columns contain NULL.

[Admin]

Many thanks for your help Mehrdad. It looks so obvious now but it really had me stumped.

[B Bulfin]

I like ISNULL(@aCode, aCode) = aCode.

[Ian Varley]

Yeah, this is the same thing as wrapping both with IsNull, which is shorter and easier to maintain.

[n8wrl]

But that won't work - NULL != NULL. All you're doing is ignoring the parameter if it's NULL and that's not what the OP wants.

[KristianB]

Upvoted, it was the most useful in my case. This enables me to skip passing a parameter at all, when I don't need it. C# null != SQL null.