We help IT Professionals succeed at work.

SQL Where Clause Null issue in a script

I am writing a SP for a search form for my VBA application.
The parameters passed are optional fields
attached is the early form of script.

The issue I have is, if any of the column in where clause is null, the script doesnt pull out that row.

For example if I passed only 1 parameter i.e. AccountName, rest are passed as null, and if the there are two rows with account name 'Test1', and one of the row has null in for example quotestatus, it doesnt pull the row with null value, I want those also to be pulled ?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[SP_LOAD_QUOTE_SELECTION_WITH_DIRECT]
(
@Superseded bit,
@AccountName nvarchar(255),
@Underwriter as nvarchar(50),
@InceptionMonth int,
@InceptionYear int,
@QuoteStatus nvarchar(50),
@Broker nvarchar(255),
@Branch nvarchar(50),
@SavedBy nvarchar(100),
@QuoteAfter datetime2(0),
@QuoteBefore datetime2(0),
@PolicyNumber nvarchar(50)
)
AS 
   
BEGIN
SET NOCOUNT ON

   SELECT 
      tQ_Policy.Id_Quote, 
      tQ_Policy.fSuperseded, 
      tQ_Policy.fBranch, 
      tQ_Policy.fQuoteStatus, 
      tQ_Policy.fAccountName, 
      tQ_Policy.fUnderwriter, 
      tQ_Policy.fBroker, 
      tRef_UserRights.fFullName AS fCreateUser, 
      tQ_Policy.fInceptionDate, 
      tQ_Policy.fCreateDate,
      tQ_Policy.fQuickPrice_QuoteStatus
   FROM 
      tRef_UserRights 
         INNER JOIN tQ_Policy 
         ON tRef_UserRights.fUserName = tQ_Policy.fCreateUser
   where fSuperseded=@Superseded AND
   fAccountName like Coalesce(@AccountName+'%' , fAccountName)  and
   fUnderwriter like Coalesce(@Underwriter+'%' , fUnderwriter)  and
   Month(fInceptionDate) = Coalesce(@InceptionMonth ,month(fInceptionDate)) and 
   Year(fInceptionDate) = Coalesce(@InceptionYear , year(fInceptionDate)) and
   fQuoteStatus = Coalesce(@QuoteStatus , fQuoteStatus) and
   fBroker like Coalesce(@Broker+'%' , fBroker) and
   fBranch like Coalesce(@Branch+'%' , fBranch) and
   fCreateUser like Coalesce(@SavedBy+'%' , fCreateUser) and
   fCreateDate >= Coalesce(@QuoteAfter , fCreateDate) and
   fCreateDate <= Coalesce(@QuoteBefore , fCreateDate) 
END

Open in new window

Comment
Watch Question

Senior Developer Analyst
Commented:
You can try ISNULL() in the where clause statements.  Something like this using the fAccountName line as an example
...

AND

ISNULL(fAccountName,'') like Coalesce(@AccountName+'%' , ISNULL(fAccountName,''))

AND

...and so on and so on...

That usually does the trick for me.  I'd also encourage you to look into SQL Full Text Indexing.  That opens up your script to use cool T-SQL like "CONTAINS" and others...

Good luck!
Top Expert 2011
Commented:
more like

 ( fAccountName like Coalesce(@AccountName+'%' ,'%')  or faccountname is null)

please be more specific is you specify a value for a parameter do you want to allow that column to be null on the row?

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I would check for NULL first, to hopefully allow SQL to skip the other check when the column actually is NULL:

   (fAccountName is null or fAccountName like Coalesce(@AccountName, '%'))  and
   (fUnderwriter is null or fUnderwriter like Coalesce(@Underwriter, '%')) and ...
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Btw, that was an intentional re-write/simplification of the COALESCE(), not a typo.
Top Expert 2011

Commented:
assumes that the parameter already contains the wildcards if present?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
True, good point.  I always add those if needed in earlier non-query logic, but they may not, so:

 (fAccountName is null or fAccountName like Coalesce(@AccountName + '%', '%'))  and

I still much prefer a '%' as the LIKE string for a NULL parameter rather than the original column value.

That way, only one character has to be compared rather than the entire length of the column value.