T-SQL Stored Procedure Input NULLs cause statement select error

Question

Below is the stored procedure for checking for duplicate records in the database based on checking all fields separately (do not ask why I should do this, it should be just that).

It sounds quite simple, but SP fails. The problem is that some parameters passed to the SP may have a null value, so sql should read "is null" and not "= null". I tried isnull (), case case, coalesce () and dynamic sql with exec () and sp_executesql and could not implement any of them. 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 similar for each parameter with probability-null:

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

Answer 2

If I understand your question correctly, then I recommend you do a little research:

 SET ANSI_NULLS OFF 

If you use this command in your stored procedure, you can use = NULL in your comparison. Take a look at the following code example to see how it 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 is recommended that you set it to ON as soon as possible, as this may affect other requests that you run later. All SET commands affect only the current session, but depending on your application, this may span multiple requests, so I suggest you turn on ansi nulls again after this request.

Answer 3

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

 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 4

First, I would add a check to see if all parameters were zero at runtime, i.e.

 IF(COALESCE(@userId, @noteType, @aCode, @bCode, @cCode) IS NULL) BEGIN -- do something here, log, print, return, etc. END 

Then, after you confirm that the user has passed something, 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: Perhaps I missed the intention that if the parameter was passed as null, then you explicitly want to test the column for null. My suggestion is above where it is assumed that the null parameter means "skip the test in this column".

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

 SET ANSI_NULLS OFF GO CREATE PROC sp_myDuplicateCheck.... 

Effectively this should allow your code to then evaluate the column = null, not the column. I think Kalen Delaney once came up with the ANSI_NULLS and QUOTED_IDENTIFIER options as “sticky options,” because if they are configured at the time the procedure was created, they remain with the procedure at run time, regardless of how the connection is established at that time.

Answer 5

Try the following:

 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 

Basically, this means providing default values ​​to the input parameters in case of null, and then in the where clause it checks only if the values ​​are not equal to the default values.

Answer 6

SET ANSI_NULLS OFF/On

This way you can do colName = null