March 6, 2013 at 2:05 pm
Hi, I am wondering if there is a standard industry name or term for this concept aside from "horrible", "bad idea", or "if you do this I (dba) will eat your first born."
I remember learning this lesson on my own several years ago, but was recently reminded of this lazy way of getting things done in a stored procedure because a consultant that was hired to help our reporting team submitted a stored procedure to me that contains a where clause like this.
A simple mockup of the scenario would look like this:
CREATE PROCEDURE GetSomeData(@Parameter1 int)
AS
BEGIN
SELECT
Field1
, Field2
, Field3
FROM SomeTable
WHERE
(Field4 = @Parameter OR @Parameter = -1)
END
The part I am asking about is the where clause, where a dummy value is passed in to the stored procedure that essentially is used to mean "return everything."
Of course, this makes the query impossible to tune, as it will always do an index scan. I've already advised him that I will not be committing his stored procedure to the database and suggested several other ways they can achieve the desired result.
Anyhow, is there a term used to describe this kind of where clause? Inquiring minds want to know!
Thanks!
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
March 6, 2013 at 2:14 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2013 at 2:26 pm
GilaMonster (3/6/2013)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Thanks for that. Sounds like a good term for it to me.
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply