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

Answer 1

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

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

Answer 2

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

Alternatively, I believe you can use your original query and add the ANSI_NULLS set option at the stored procedure create time. For example,

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

Its really a nice code. This code resolved my problem. Thankx

Answer 7

SET ANSI_NULLS OFF/On -> This way u can do colName = null