title: T-SQL Stored Procedure NULL input values cause select statement to fail
body: <p>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). </p>
  
  <p>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...</p>
  
  <pre><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
  </code></pre>