Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic Where Clause for Multiple paramaters with AND Operator Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 5:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
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);

Post #1402126
Posted Wednesday, January 2, 2013 6:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 PM
Points: 21,351, Visits: 15,031
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1402140
Posted Wednesday, January 2, 2013 7:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause
Post #1402145
Posted Wednesday, January 2, 2013 7:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 PM
Points: 21,351, Visits: 15,031
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1402147
Posted Wednesday, January 2, 2013 8:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
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)
Post #1402155
Posted Wednesday, January 2, 2013 10:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
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);



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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1402170
Posted Thursday, January 3, 2013 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
So any chance of building the Dynamic Where Clause Query for the above statements assuming that some parameters could be NULL ..Thanks In Advance
Post #1402311
Posted Thursday, January 3, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
Take a look at Gail's post. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1402368
Posted Sunday, January 6, 2013 6:06 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 452, Visits: 849
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.
Post #1403384
Posted Sunday, January 6, 2013 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1403390
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse