Parameter problems
-
Hi there. I have the following stored procedure: /**********************************************/ CREATE PROCEDURE sp_iet_ComplaintReport @StartDate DATETIME, @EndDate DATETIME AS select co.ComplaintID, pli.Description AS RegardingDD, co.DateRaised, c._accCustomercode, rtrim(c.firstnames) + ', ' + rtrim(c.lastnames) as Name, CASE WHEN co.BeenRead = 0 THEN 'Open' WHEN co.BeenRead = 1 THEN 'Completed' END AS Status, rtrim(u.fullname) as handler from complaint co inner join contact c on co.contactid = c.contactid inner join adm_picklistitem pli on co.regardingdd = pli.localcode and pli.picklistname = 'complaintlist' left outer join adm_user u on co.handler = u.userid where co.dateraised >= @startdate and co.dateraised <= @enddate GO /*********************************************/ This works fine but i want to be able to pass @Handler and map that to the complaint.Handler field. The thing that i want is if i pass NULL to the @Handler then it should bring back all Complaint.Handlers but if you pass an actual value to @Handler then this should bring back a matching value (if it exists). Sorry if I have worded poorly. Any help would be greatly appreciated.
-
Hi there. I have the following stored procedure: /**********************************************/ CREATE PROCEDURE sp_iet_ComplaintReport @StartDate DATETIME, @EndDate DATETIME AS select co.ComplaintID, pli.Description AS RegardingDD, co.DateRaised, c._accCustomercode, rtrim(c.firstnames) + ', ' + rtrim(c.lastnames) as Name, CASE WHEN co.BeenRead = 0 THEN 'Open' WHEN co.BeenRead = 1 THEN 'Completed' END AS Status, rtrim(u.fullname) as handler from complaint co inner join contact c on co.contactid = c.contactid inner join adm_picklistitem pli on co.regardingdd = pli.localcode and pli.picklistname = 'complaintlist' left outer join adm_user u on co.handler = u.userid where co.dateraised >= @startdate and co.dateraised <= @enddate GO /*********************************************/ This works fine but i want to be able to pass @Handler and map that to the complaint.Handler field. The thing that i want is if i pass NULL to the @Handler then it should bring back all Complaint.Handlers but if you pass an actual value to @Handler then this should bring back a matching value (if it exists). Sorry if I have worded poorly. Any help would be greatly appreciated.
In your where clause try the following
Where (Isnull(@Handler,'') = '') or Handler = @HandlerIf the test for @handler = '' then no filter is applied, if @handler has a value then the filter is used. Caveat - many of these in a single query can reduce performance!Never underestimate the power of human stupidity RAH