This has always been a sticking point for me, it always seemed to lead to "dirty" solutions. This is an approach I came up with to accommodate a flexible user interface. There are about 8 different fields in the original that can be selected from beyond the date range, and the user can select 0 - many items from each field.

This passes all of the selection into the proc in a table type parameter as name value pairs. If you needed to select two different departments for instance you would pass in two different records with the same parameter name, but different parameter values.

There haven't been any performance issues, but the primary table is only about 60,000 rows at this point. I do like that it allows for flexibility with a minimum of fuss, and no dynamic SQL.

CREATE PROCEDURE [dbo].[usp_GetUserSelection]

@paramVal dbo.tdt_ParameterData READONLY

AS

BEGIN

SET NOCOUNT ON;

-- Create internal parameter table so that we can modify values.

DECLARE @paramValues TABLE

(

ParameterName varchar(50),

ParameterValue varchar(200) NULL

);

-- Copy the incoming parameters over.

INSERT INTO @paramValues

(

ParameterName,

ParameterValue

)

SELECT

LTRIM(RTRIM(ParameterName)),

LTRIM(RTRIM(ParameterValue))

FROM @paramVal;

-- Get start and end date, these are the only required params.

DECLARE @startDate datetime = (SELECT CAST(ParameterValue as datetime) FROM @paramValues WHERE ParameterName = 'StartDate');

DECLARE @endDate datetime = (SELECT CAST(ParameterValue as datetime) FROM @paramValues WHERE ParameterName = 'EndDate');

-- Add a null value placeholder for any unused parameters.

IF NOT EXISTS (SELECT ParameterValue FROM @paramValues WHERE ParameterName = 'DeptNumber')

INSERT INTO @paramValues (ParameterName, ParameterValue) VALUES('DeptNumber', NULL);

IF NOT EXISTS (SELECT ParameterValue FROM @paramValues WHERE ParameterName = 'LocationCode')

INSERT INTO @paramValues (ParameterName, ParameterValue) VALUES('LocationCode', NULL);

SELECT

t.ItemID,

ISNULL(lm.LocationCode, '') LocationCode,

ISNULL(lm.[Description], '') LocationDesc,

ISNULL(lm.DeptNumber, '') DeptNumber,

ISNULL(dd.DepartmentDescription, '') DepartmentDescription

FROM dbo.Item t

LEFT OUTER JOIN dbo.Location lm ON t.LocationID = lm.LocationID

LEFT OUTER JOIN dbo.Department dd ON lm.DeptNumber = dd.DepartmentNumber

JOIN @paramValues p0 ON p0.ParameterName = 'LocationCode'

AND lm.LocationID = ISNULL(p0.ParameterValue, lm.LocationID)

JOIN @paramValues p1 ON p1.ParameterName = 'DeptNumber'

AND lm.DeptNumber = ISNULL(p1.ParameterValue, lm.DeptNumber)

WHERE

(

t.EndDate >= @startDate

AND t.StartDate <= @endDate

)

AND

(

(

p0.ParameterValue IS NOT NULL

OR p1.ParameterValue IS NOT NULL

)

OR

(

@startDate IS NOT NULL

AND @endDate IS NOT NULL

)

);

END