Stored Procedure Null problem

[anorthcote]

Hi

I have the following storec procedure

--
CREATE PROCEDURE CallsEnquiry

@LocationID int,
@StatusID int,
@Area int,
@Contractor varchar(60) AS
BEGIN

SELECT dbo.Calls.LogID, dbo.Calls.EnteredBy, dbo.Calls.Entered, dbo.Calls.LocationID, sz.dbo.Location.Branch,
CAST(SUBSTRING(sz.dbo.Area.Name, 6, 2) AS int) AS Area, dbo.Calls.ProblemText, dbo.Calls.OrderID,
case when sz.dbo.Contractor.Name is null then '-' else sz.dbo.Contractor.Name end AS Contractor, dbo.Calls.DealtWith, dbo.Calls.FixBy, dbo.Calls.DateCompleted, dbo.Calls.ConfirmedBy,
dbo.Calls.StatusID, dbo.Calls.Comments
FROM sz.dbo.Contractor INNER JOIN
sz.dbo.ContractorOrder ON sz.dbo.Contractor.ID = sz.dbo.ContractorOrder.ContractorID RIGHT OUTER JOIN
dbo.Calls ON sz.dbo.ContractorOrder.OrderID = dbo.Calls.OrderID LEFT OUTER JOIN
sz.dbo.Area INNER JOIN
sz.dbo.Location ON sz.dbo.Area.ID = sz.dbo.Location.AreaID ON dbo.Calls.LocationID = sz.dbo.Location.Code


where LocationID = (case @LocationID when 0 then LocationID else @LocationID end)
and StatusID = (case @StatusID when 0 then StatusID else @StatusID end)
and cast(substring(sz.dbo.Area.Name,6,2) as int) = (case @area when 0 then cast(substring(sz.dbo.Area.Name,6,2) as int) else @area end)
and sz.dbo.Contractor.Name = (case @Contractor when '' then sz.dbo.Contractor.Name else @Contractor end)

order by LogID


END
GO

--

This is to be executed by a vb script passing four variables. Dependant on what values the variables are passes as depends on what the 'where' statement does.

It works well apart from where there is a NULL value in the result, the 'where' statement seems to filter the records with NULL in any column out.

Can anyone help with a better way of doing my 'where' selection?

 

[bborissov]

where (@LocationID = 0  OR LocationID  = @LocationID) AND
      (@StatusID   = 0  OR StatusID    = @StatusID)   AND
      (@area       = 0  OR cast(substring(sz.dbo.Area.Name,6,2) as int)  = @area) AND
      (@Contractor = '' OR sz.dbo.Contractor.Name = @Contractor)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[anorthcote]

That's great, thanks!!

How does it work, I'm confused???