RamSteve (1/2/2013)

---

I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause

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);

CAST(@EndDate AS VARCHAR)

CAST(VARCHAR(19), @EndDate,120)

CAST(VARCHAR(23), @EndDate,121)

SELECT @WhCaseDispoID=''
    ,@WhStartDate=''
    ,@WhEndDate=''

IF @CaseDispoID IS NOT NULL
    SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''
ELSE IF @StartDate IS NOT NULL
    SET @WhStartDate =' AND ce.CreationTime>='''+CAST(VARCHAR(19), @StartDate,120)+''''
ELSE IF @EndDate IS NOT NULL
    SET @WhEndDate =' AND ce.CreationTime<='''+CAST(VARCHAR(19), @EndDate,120)+''''
ELSE SET @WhEndDate =' AND ce.CreationTime <GetDate()'

dwain.c (1/2/2013)

You should use this instead:

CAST(VARCHAR(19), @EndDate,120)

Or if additional precision is required (milliseconds) use:

CAST(VARCHAR(23), @EndDate,121)

davoscollective (1/6/2013)

---

I think that should have been convert, not cast. I'm sure just a typo.