SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Where Clause for Multiple paramaters with AND Operator


Dynamic Where Clause for Multiple paramaters with AND Operator

Author
Message
RamSteve
RamSteve
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 640
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);
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67093 Visits: 18570
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

RamSteve
RamSteve
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 640
I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67093 Visits: 18570
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

RamSteve
RamSteve
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 640
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)
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17959 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
RamSteve
RamSteve
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 640
So any chance of building the Dynamic Where Clause Query for the above statements assuming that some parameters could be NULL ..Thanks In Advance
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63061 Visits: 17959
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 Modens 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)
davoscollective
davoscollective
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1673 Visits: 1008
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17959 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search