January 2, 2013 at 5:42 pm
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);
January 2, 2013 at 6:40 pm
Other than Case 6, do you expect to have the ability to have multiple conditions in the where clause?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 2, 2013 at 7:05 pm
I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause
January 2, 2013 at 7:08 pm
RamSteve (1/2/2013)
I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause
So of the 9 parameters you listed, which 6 are possible? Or are you saying that you can have up to 6 parameters at once?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 2, 2013 at 8:33 pm
HI in the above 9 parameters it can have only one based on case statement or it could be NULL for example
In @Where parameter i was giving cu.customerNumber Like '%1234%' or ce.CustomerEventID Like '%12334%' ..etc
So @Where parameter and @SerchValue are related and Rest of the parameters (@CaseDispoID ,StartDate ,EndDate) can have one value or could be NULL so the final query will be like below if you have all the parameters
select * from abc Where ce.CustomerEventID Like '%12345%' and cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATE
So for example in the above Query IF the Parameters @Where and @SearchValue is NULL then the query looks like below
select * from abc where cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATE
So my whole point is to build Dynamic Where Clause Query using AND Operator based on parameters i get in case statement for each parameter(@Where,@WhSearchValue,@WhCaseDispoID,@WhStartDate,@WhEndDate)
January 2, 2013 at 10:08 pm
RamSteve (1/2/2013)
Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find myQuery 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);
I see a couple of issues here:
1. Note where I have made some of your script bold. In those cases, you are missing a blank before either the AND or the LIKE.
2. This CAST of the DATETIME variable may not give you a format the SQL can easily compare (and it is likely to be truncated):
CAST(@EndDate AS VARCHAR)
You should use this instead:
CAST(VARCHAR(19), @EndDate,120)
Or if additional precision is required (milliseconds) use:
CAST(VARCHAR(23), @EndDate,121)
You can also significantly condense all the statements after the assignment to @WhSearchValue, something like this:
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()'
That last assuming of course that only one of the 3 input parameters is NOT NULL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 3, 2013 at 5:43 am
So any chance of building the Dynamic Where Clause Query for the above statements assuming that some parameters could be NULL ..Thanks In Advance
January 3, 2013 at 7:25 am
Take a look at Gail's post. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
She explains how to deal with this type of thing very cleanly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2013 at 6:06 pm
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)
I think that should have been convert, not cast. I'm sure just a typo. 120 also assumes you are using MDY ordering.
I am wondering about this part in the original query (and similar parts):
'AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''
If the CreationTime field is a datetime, then you should compare it to @StartDate parameter as a datetime. Apart from NULLs it should then behave as intended.
If the CreationTime field is a varchar, you might be safer to cast CreationTime as a datetime before comparing it to @StartDate, rather than casting @StartDate as a varchar.
Comparing them both as varchars may well implicitly convert both to datetime values prior to comparison but you can't guarantee that and it might give unexpected results, particularly if this is a manually entered field in the front end application which could have dodgy values entered.
January 6, 2013 at 6:42 pm
davoscollective (1/6/2013)
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)I think that should have been convert, not cast. I'm sure just a typo.
Yup. You're correct.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply