Problem with Null values for Search

[Zirak]

hi,
I have a Stored Procedure for search which is based on multiple joins.
The SP is at the bottom of this message.
My problem is that I have set up the default values as %, there for for example if I don't provide one parameter it will consider any value. The problem is that if that value is NULL the SP will not return that record. Now if I change the Where clause to,for example:

dbo.tblComIssue.ContactID LIKE @RCaller OR dbo.tblComIssue.ContactID IS NULL,

then the result set will consist of Null values but it will return all records with Null values for that field.

I hope you understand my question.

Thanks










------------------------------------------------------
CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@RCaller varchar(50) = '%',
@RPropMgr varchar(50) = '%',
@RDate varchar(50) = '%',
@RComStat varchar(50) = '%',
@ROpsMgr varchar(50) = '%',
@RBID varchar(50) = '%')
AS SELECT dbo.tblComIssue.ComID, dbo.tblProperty.AVPID, dbo.tblProperty.OpsMgrID, dbo.tblProperty.ProjectMgrID, dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle, dbo.tblProperty.Division, dbo.tblComIssue.ComDate, dbo.tblComIssue.ComType, dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller, ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate, dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From], dbo.tblComResponse.ResID, dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName, CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr, dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID
WHERE (dbo.tblComIssue.ContactID LIKE @RCaller ) AND (dbo.tblProperty.ContactID LIKE @RPropMgr ) AND (dbo.tblComIssue.IssueClosed LIKE @RComStat ) AND (dbo.tblProperty.OpsMgrID LIKE @ROpsMgr ) AND (dbo.tblProperty.BuildingID LIKE @RBID) AND (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE CONVERT(Varchar(10), @RDate, 101) )
GO

 

[cehepker]

Hello,

I was wondering is you could use isnull to change the value of the paramater, to '%' for example, either at the beginning of the stored procedure, or down in the WHERE statement. At the beginning of the procedure would probably look cleaner.

Documentation: A Shaft of light into a Coded world

 

[Stokleberry]

You're using isnull in the select statement. Is there any reason you can't use it in the where statement. That's what I do to handle nulls.