|
|
You're in the
sql server programming
group:help with conditions in WHERE clause
sql server programming:
e.g. WHERE 1 = CASE @ReturnTimedOrNotTimed WHEN 'N' THEN case when PickupTime IS NULL then 1 else 0 end WHEN 'U' THEN case when PickupTime IS NOT NULL then 1 else 0 end END [quoted text, click to view] "Keith G Hicks" wrote:
> In a stored procedure (sql 2k) I'm passing a parameter for whehter or not > the result set returns rows if a column is null. > > @ReturnTimedOrNotTimed CHAR(1) > > IF @ReturnTimedOrNotTimed = 'T' > SELECT ...... > FROM ... > WHERE > PickupTime IS NOT NULL > ELSE IF @ReturnTimedOrNotTimed = 'N' > SELECT ...... > FROM ... > WHERE > PickupTime IS NULL > ELSE IF @ReturnTimedOrNotTimed = 'B' > SELECT ...... > FROM ... > > I'd rather avoid restating the entire query 3 times and I know I should > avoid dynamic sql where possible. I've been trying to figure out how to put > the @ReturnTimedOrNotTimed for all 3 cases in the WHERE part of one query > but what I tried slows it down a lot (3 times slower). > > SELECT ...... > FROM ... > WHERE > (@ReturnTimedOrNotTimed = 'N' AND PickupTime IS NULL) > OR > (@ReturnTimedOrNotTimed = 'T' AND PickupTime IS NOT NULL) > OR > (@ReturnTimedOrNotTimed = 'B') -- "B" is for "Both" > > I get the result set I expect for each value of @ReturnTimedOrNotTimed but > again, it's much slower than the first way. Is there a good way to write a > where clause that depends on an input parameter like this? I tried a CASE > construct in the WHERE but couldn't get it to even compile. Not sure if > that's possible. > > SELECT ...... > FROM ... > WHERE > CASE @ReturnTimedOrNotTimed > WHEN 'N' THEN PickupTime IS NULL > WHEN 'U' THEN PickupTime IS NOT NULL > END > > TIA, > > Keith > > >
In a stored procedure (sql 2k) I'm passing a parameter for whehter or not
the result set returns rows if a column is null. @ReturnTimedOrNotTimed CHAR(1) IF @ReturnTimedOrNotTimed = 'T' SELECT ...... FROM ... WHERE PickupTime IS NOT NULL ELSE IF @ReturnTimedOrNotTimed = 'N' SELECT ...... FROM ... WHERE PickupTime IS NULL ELSE IF @ReturnTimedOrNotTimed = 'B' SELECT ...... FROM ... I'd rather avoid restating the entire query 3 times and I know I should avoid dynamic sql where possible. I've been trying to figure out how to put the @ReturnTimedOrNotTimed for all 3 cases in the WHERE part of one query but what I tried slows it down a lot (3 times slower). SELECT ...... FROM ... WHERE (@ReturnTimedOrNotTimed = 'N' AND PickupTime IS NULL) OR (@ReturnTimedOrNotTimed = 'T' AND PickupTime IS NOT NULL) OR (@ReturnTimedOrNotTimed = 'B') -- "B" is for "Both" I get the result set I expect for each value of @ReturnTimedOrNotTimed but again, it's much slower than the first way. Is there a good way to write a where clause that depends on an input parameter like this? I tried a CASE construct in the WHERE but couldn't get it to even compile. Not sure if that's possible. SELECT ...... FROM ... WHERE CASE @ReturnTimedOrNotTimed WHEN 'N' THEN PickupTime IS NULL WHEN 'U' THEN PickupTime IS NOT NULL END TIA, Keith
Keith G Hicks (krh@comcast.net) writes:
[quoted text, click to view] > I'd rather avoid restating the entire query 3 times and I know I should > avoid dynamic sql where possible. I've been trying to figure out how to > put the @ReturnTimedOrNotTimed for all 3 cases in the WHERE part of one > query but what I tried slows it down a lot (3 times slower). These types of searches often gets the best performance with dynamic SQL, but if there is only one parameter I would probably not go for dynamic SQL myself. Here is a variation that only requires you to repeat the query twice: SELECT .... FROM .... WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) UNION SELECT ... FROM ... WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND PickupTime IS NOT NULL) [quoted text, click to view] > I get the result set I expect for each value of @ReturnTimedOrNotTimed > but again, it's much slower than the first way. Is there a good way to > write a where clause that depends on an input parameter like this? I > tried a CASE construct in the WHERE but couldn't get it to even compile. > Not sure if that's possible. Nigel's post gave you the syntax for CASE, but I would not expect this to perform any better. The problem is that as soon you put that open- ended variable, the query plan must cover all possible values for it, since the value of the variable is not known to the optimizer when the plan is built. (Parameter sniffing? Yes, the value can be used as guidance, but the optimizer must build a plan which still produces the correct result if the parameter is changed along the way.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Thanks Nigel. That makes sense. :-)
Keith [quoted text, click to view] "Nigel Rivett" <NigelRivett@discussions.microsoft.com> wrote in message The case statement returns a value which you need to testnews:74E97765-5470-468B-BE7B-36DAEFFD6286@microsoft.com... e.g. WHERE 1 = CASE @ReturnTimedOrNotTimed WHEN 'N' THEN case when PickupTime IS NULL then 1 else 0 end WHEN 'U' THEN case when PickupTime IS NOT NULL then 1 else 0 end END
Oh, good idea. Sometimes I get so burried in one way of doign things, I
forget to step back and look for another approach. :-) [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns98AB73AED4AFBYazorman@127.0.0.1... Keith G Hicks (krh@comcast.net) writes: > I'd rather avoid restating the entire query 3 times and I know I should > avoid dynamic sql where possible. I've been trying to figure out how to > put the @ReturnTimedOrNotTimed for all 3 cases in the WHERE part of one > query but what I tried slows it down a lot (3 times slower). These types of searches often gets the best performance with dynamic SQL, but if there is only one parameter I would probably not go for dynamic SQL myself. Here is a variation that only requires you to repeat the query twice: SELECT .... FROM .... WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) UNION SELECT ... FROM ... WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND PickupTime IS NOT NULL) [quoted text, click to view] > I get the result set I expect for each value of @ReturnTimedOrNotTimed > but again, it's much slower than the first way. Is there a good way to > write a where clause that depends on an input parameter like this? I > tried a CASE construct in the WHERE but couldn't get it to even compile. > Not sure if that's possible. Nigel's post gave you the syntax for CASE, but I would not expect this to perform any better. The problem is that as soon you put that open- ended variable, the query plan must cover all possible values for it, since the value of the variable is not known to the optimizer when the plan is built. (Parameter sniffing? Yes, the value can be used as guidance, but the optimizer must build a plan which still produces the correct result if the parameter is changed along the way.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Uri Dimant (urid@iscar.co.il) writes:
[quoted text, click to view] >> SELECT .... >> FROM .... >> WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) >> UNION >> SELECT ... >> FROM ... >> WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND >> PickupTime IS NOT NULL) > > UNION or UNION ALL :-)))) UNION! Why is left as an exercise to the reader. :-) [quoted text, click to view] > Happy new year , and waiting for more new acrticles covering SQL Server > 2005 Happy New Year to you, Uri, and everyone else! -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Erland Sommarskog (esquel@sommarskog.se) writes:
[quoted text, click to view] > Uri Dimant (urid@iscar.co.il) writes: >>> SELECT .... >>> FROM .... >>> WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) >>> UNION >>> SELECT ... >>> FROM ... >>> WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND >>> PickupTime IS NOT NULL) >> >> UNION or UNION ALL :-)))) > > UNION! Why is left as an exercise to the reader. :-) Stupid me! I was thinking that since we have the condition @ReturnTimedOrNotTimed = 'B' in both SELECT; we need UNION to weed out the duplicates. But since the conditions on PickupTime are mutually exclusive, UNION ALL is of course what we should use. Thanks, Uri, for setting me straight! -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Erland
[quoted text, click to view] > SELECT .... > FROM .... > WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) > UNION > SELECT ... > FROM ... > WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND > PickupTime IS NOT NULL) UNION or UNION ALL :-)))) Happy new year , and waiting for more new acrticles covering SQL Server 2005 [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns98AB73AED4AFBYazorman@127.0.0.1... > Keith G Hicks (krh@comcast.net) writes: >> I'd rather avoid restating the entire query 3 times and I know I should >> avoid dynamic sql where possible. I've been trying to figure out how to >> put the @ReturnTimedOrNotTimed for all 3 cases in the WHERE part of one >> query but what I tried slows it down a lot (3 times slower). > > These types of searches often gets the best performance with dynamic SQL, > but if there is only one parameter I would probably not go for dynamic SQL > myself. > > Here is a variation that only requires you to repeat the query twice: > > SELECT .... > FROM .... > WHERE (@ReturnTimedOrNotTimed IN ('N', 'B') AND PickupTime IS NULL) > UNION > SELECT ... > FROM ... > WHERE (@ReturnTimedOrNotTimed IN ('T', 'B') AND > PickupTime IS NOT NULL) > >> I get the result set I expect for each value of @ReturnTimedOrNotTimed >> but again, it's much slower than the first way. Is there a good way to >> write a where clause that depends on an input parameter like this? I >> tried a CASE construct in the WHERE but couldn't get it to even compile. >> Not sure if that's possible. > > Nigel's post gave you the syntax for CASE, but I would not expect this > to perform any better. The problem is that as soon you put that open- > ended variable, the query plan must cover all possible values for it, > since the value of the variable is not known to the optimizer when the > plan is built. (Parameter sniffing? Yes, the value can be used as > guidance, but the optimizer must build a plan which still produces the > correct result if the parameter is changed along the way.) > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[quoted text, click to view]
"Keith G Hicks" <krh@comcast.net> wrote in message news:upY9XscLHHA.3872@TK2MSFTNGP06.phx.gbl... > . > Sometimes I get so burried in one way of doign things, I > forget to step back and look for another approach. :-) > I'm forwarding this thought to MS....and the White House. best wishes for the new year,
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |