Kevin,

We use the COALESCE() function, it makes the code a whole lot easier to maintain, here's an example:

WHERE p.AppKey                 = COALESCE(@AppKey, p.AppKey) 
  AND pc.CategoryTypeKey       = COALESCE(@CategoryKey,pc.CategoryTypeKey  
  AND psc.ProjectStatusCodeKey = COALESCE(@StatusKey,psc.ProjectStatusCodeKey)

Basically for each variable, if the variable is null, then it will retrieve all values, otherwise it will only pull in records that match the variable. It is very easy to add additional filter criteria.

Kurt

>I have this proc:
>
>

>CREATE PROCEDURE bm_GetProjects
>	@AppKey			int,
>	@CategoryKey	int,
>	@StatusKey		int
>
>AS
>BEGIN
>
>	SELECT p.ProjectKey, p.ProjectName, p.ProjectCode, p.RateAmt, p.EstHours, p.EstStartDate,
>		   p.EstEndDate, p.TotalHours, p.StartDate, p.EndDate, p.PercentDone,
>		   p.CategoryKey, pc.CategoryName,
>		   p.RateTypeKey, rt.TypeName AS RateTypeName,
>		   p.StatusKey, psc.ProjectStatusCodeName AS StatusCode,
>		   p.PriorityKey, pr.PriorityName AS Priority,
>		   p.ManagerKey, LTRIM(RTRIM(e.LastName)) + ' ' + e.FirstName AS ManagerName
>		FROM bm_Projects p
>		JOIN bm_RateTypes rt ON rt.TypeKey = p.RateTypeKey
>		JOIN bm_PrjCategories pc ON pc.CategoryTypeKey = p.CategoryKey
>		JOIN bm_Priorities pr ON pr.PriorityKey = p.PriorityKey
>		JOIN bm_ProjectStatusCodes psc ON psc.ProjectStatusCodeKey = p.StatusKey
>		LEFT JOIN bm_Employees e ON e.EmployeeKey = p.ManagerKey
>		WHERE p.AppKey = @AppKey AND
>			  pc.CategoryTypeKey = @CategoryKey AND
>			  psc.ProjectStatusCodeKey = @StatusKey
>		ORDER BY ProjectName
>
>END
>
>

>
>It's possible for any combination of parameters to be passed. How do I change the WHERE clause to handle this?
>
>Thanks