RamSteve (1/2/2013)

---

Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find my

Query as Below and I need to make AND Operator to be dynamic instead of hard coding it as i was doing it as below

so the query will look as select * from xyx where cu.unitNumber like '%@SearchValue%' AND cd.CaseDispoID=5 and StartDate >= '12/01/2012' and EndDate<=GetDate()

Thanks In Advance...

SELECT @Where =CASE @SearchBy

WHEN 1 THEN 'WHERE CAST(Ce.CustomerEventID AS VARCHAR)'

WHEN 2 THEN 'WHERE u.UnitNumber'

WHEN 3 THEN 'WHERE l.LocationNumber'

WHEN 4 THEN 'WHERE q.SPName'

WHEN 5 THEN 'WHERE ce.UserName'

WHEN 6 THEN 'WHERE c.City + c.StateName'

WHEN 7 THEN 'WHERE ce.UserName'

WHEN 8 THEN 'WHERE cu.Name'

WHEN 9 THEN 'WHERE cu.CustomerNumber'

ELSE

'WHERE'

END

IF(@SearchValue IS NOT NULL)

BEGIN

SET @WhSearchValue ='LIKE ''%'+@SearchValue+'%'' '

END

ELSE

BEGIN

SET @WhSearchValue =' LIKE''%'++'%'' '

END

IF(@CaseDispoID IS NOT NULL)

BEGIN

SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''

END

ELSE

BEGIN

SET @WhCaseDispoID=''

END

IF(@StartDate IS NOT NULL)

BEGIN

SET @WhStartDate ='AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''

END

ELSE

BEGIN

SET @WhStartDate=''

END

IF(@EndDate IS NOT NULL)

BEGIN

SET @WhEndDate ='AND ce.CreationTime<='''+CAST(@EndDate AS VARCHAR)+''''

END

ELSE

BEGIN

SET @WhEndDate ='AND ce.CreationTime <= GetDate()'

END

SELECT @SQL= '

'+@Select+'

'+@From+'

'+@Where+'

'+@WhSearchValue+'

'+@WhCaseDispoID+'

'+@WhStartDate+'

'+@WhEndDate+'

'

EXEC (@SQL);