Prev: Optimizer on SQL 2005
Next: Restore using MDF and LDF
From: Abba on 3 Feb 2010 21:31
I will go thru this and let you know. Thanks Frank.

"Frank Uray" <FrankUray(a)discussions.microsoft.com> wrote in message
news:3183DF43-65DA-413C-8262-9D5903D50ACD(a)microsoft.com...
> Here some additional informations:
>
> I am against using CASE expressions in the WHERE clause of a query to
> express conditional logic. I prefer to have everything translated to
> simple
> AND's, OR's and NOT's to keep things a) portable, b) easier to read and c)
> efficient.
> Learning some good boolean logic techniques will go a long way towards
> making your queries more efficient, and you won't need to rely on CASE's
> and
> other methods of doing conditional logic.
> First off, when I say "conditional logic", I am talking about something
> like
> this:
> "If A then B"
> where A and B are both conditions. For example, in a WHERE clause, you
> might want to implement a condition like this:
> "If (@ReturnAll <>1) THEN (EmpID = @EmpID)"
> To express this logic in the WHERE clause, many people might code it like
> this:
> WHERE EmpID = CASE WHEN @ReturnAll<>1 THEN @EmpID ELSE EmpID END
> However, this is kind of counter-intuitive (why should we check that EmpID
> =
> EmpID ?) and can be really tough to implement when the condition spans
> more
> than 1 column in the table (you need multiple CASE's). Also, if EmpID is
> null this will fail.
> The alternative is to translate the condition into a regular boolean
> expression using only AND, OR and NOT. The logical translation of "IF A
> then
> B" is:
> "(Not A) or B"
> If you work it out on paper, you will see it makes sense. To translate
> our
> WHERE clause requirement using the above logic, it becomes:
> WHERE (@ReturnAll =1) OR (EmpID = @EmpID)
> We are saying if @ReturnAll is 1, don't bother evaluating more of the
> condition -- return the row because the expression is TRUE. But if
> @ReturnAll <> 1, then EmpID must be equal to @EmpID for the condition to
> be
> true and the row to be returned. Exactly the conditional logic we wish to
> express.
> To show another example, suppose we wish to say:
> "IF @Filter=1 THEN Date= @Date and Cust= @Cust and Emp= @Emp"
> Expressing this in a CASE clause results in:
> WHERE Date = CASE WHEN @Filter=1 THEN @Date ELSE Date END AND
> Cust = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END AND
> Emp = CASE WHEN @Filter=1 THEN @Emp ELSE Emp END
> A little hard to read and quite inefficient -- all 3 case expressions must
> be evaluated for each row in the result set. Without CASE, we get:
> WHERE @Filter<>1 OR (Date= @Date and Cust= @Cust and Emp= @Emp)
> Much eaiser to read and maintain, and faster -- if @Filter <>1, the rest
> of
> the expression can be ignored by the optimizer. (This is in fact what SQL
> Server does).*
> Finally, to express:
> IF A THEN B ELSE C
> you would write it as:
> ((Not A) or B) AND (A or C)
> a little harder, but it does the job! No need for CASE in the WHERE
> clause
> ... trust me !
> (of course, you may need CASE to manipulate some columns or expressions in
> the WHERE, just don't use it for boolean logic).
> ________________________________________
> *To test this, just write: SELECT 1 WHERE 1=1 OR (1/0)=0 or Null=Null.
> Notice that the "1" is returned w/o generating an error, and also the
> Null=Null check has no effect on the results.
>
>
>
>
> "Abba" wrote:
>
>> Hello,
>>
>> [SQL2008]
>>
>> CREATE PROCEDURE [dbo].[uspGdet_temp]
>> @filby Varchar(10),
>> @filval Varchar(50)
>> AS
>> BEGIN
>> SET NOCOUNT ON
>> IF @filby = 'AcNum'
>> BEGIN
>> SELECT * FROM Acc
>> WHERE [AcNum] LIKE @filval + '%'
>> END
>> ELSE
>> BEGIN
>> SELECT * FROM Reg
>> WHERE CASE @filby
>> WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%'
>> WHEN 'FNme' THEN [FirstName] LIKE @filval + '%'
>> WHEN 'LNme' THEN [LastName] LIKE @filval + '%'
>> WHEN 'CmpNme' THEN [CompanyName] LIKE @filval +
>> '%'
>> WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%'
>> WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM
>> DAcc
>> WHERE DAccNum = @filval)
>> ELSE 1=1
>> END
>> END
>> END
>>
>>
>> While executing this, Iam getting this error:
>> Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16
>> Incorrect syntax near the keyword 'LIKE'.
>> Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22
>> Incorrect syntax near the keyword 'ELSE'.
>> Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25
>> Incorrect syntax near 'END'.
>>
>>
>> Can someone please help?
>>
>> tia,
>> AbbA
>>
>>
>> .
>>


From: Abba on 3 Feb 2010 21:34
Good suggestion Paul. However, this stored procedure already exists in
production and so changing the params is too much work.
Thank you.

"Paul Shapiro" <paul(a)hideme.broadwayData.com> wrote in message
news:%23M6KpKNpKHA.5508(a)TK2MSFTNGP02.phx.gbl...
> "Abba" <sql_help(a)aaa.com> wrote in message
> news:OfxBBtKpKHA.3776(a)TK2MSFTNGP04.phx.gbl...
>> Hello,
>>
>> [SQL2008]
>>
>> CREATE PROCEDURE [dbo].[uspGdet_temp]
>> @filby Varchar(10),
>> @filval Varchar(50)
>> AS
>> BEGIN
>> SET NOCOUNT ON
>> IF @filby = 'AcNum'
>> BEGIN
>> SELECT * FROM Acc
>> WHERE [AcNum] LIKE @filval + '%'
>> END
>> ELSE
>> BEGIN
>> SELECT * FROM Reg
>> WHERE CASE @filby
>> WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%'
>> WHEN 'FNme' THEN [FirstName] LIKE @filval + '%'
>> WHEN 'LNme' THEN [LastName] LIKE @filval + '%'
>> WHEN 'CmpNme' THEN [CompanyName] LIKE @filval +
>> '%'
>> WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%'
>> WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM
>> DAcc WHERE DAccNum = @filval)
>> ELSE 1=1
>> END
>> END
>> END
>>
>>
>> While executing this, Iam getting this error:
>> Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16
>> Incorrect syntax near the keyword 'LIKE'.
>> Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22
>> Incorrect syntax near the keyword 'ELSE'.
>> Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25
>> Incorrect syntax near 'END'.
>
> Instead of @filterBy and @filterValue, you could use a separate parameter
> for each potential filter, with the parameters all defaulted to Null and
> your code ignoring the value if it's null. That would also allow multiple
> active filters in a request. The SQL would be something like:
>
> Create Procedure
> @emailFilter Varchar(50) = Null
> ,@firstnameFilter Varchar(50) = Null
> ...
> Select * From Reg
> Where
> @emailFilter Is Null Or [Email] Like @emailFilter + '%'
> And @firstnameFilter Is Null Or [FirstName] Like @firstnameFilter + '%'
> ...


From: Abba on 4 Feb 2010 01:11
Awesome! Thank you v very much, Plamen.

Now it would take sometime for me to understand the logic ;)

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:MpudnbHr_eyLEfTWnZ2dnUVZ_jBi4p2d(a)speakeasy.net...
> Hugo already provided the best solution to this. Here is just a way to
> write it using a CASE expression:
>
> SELECT <columns>
> FROM Reg
> WHERE CASE WHEN @filby = 'Eml' AND [EMAIL] LIKE @filval + '%' THEN 'T'
> WHEN @filby = 'FNme' AND [FirstName] LIKE @filval + '%' THEN
> 'T'
> WHEN @filby = 'LNme' AND [LastName] LIKE @filval + '%' THEN 'T'
> WHEN @filby = 'CmpNme' AND [CompanyName] LIKE @filval + '%'
> THEN 'T'
> WHEN @filby = 'LoginId' AND [LoginID] LIKE @filval + '%' THEN
> 'T'
> WHEN @filby = 'AcntLog' AND [ID] IN (SELECT RegID FROM DAcc
> WHERE DAccNum = @filval) THEN 'T'
> ELSE 'F'
> END = 'T';
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Hugo Kornelis on 4 Feb 2010 03:59
On Thu, 4 Feb 2010 08:03:17 +0530, Abba wrote:

>Thanks Hugo. This is exactly the existing code. Iam trying to see if I can
>use CASE.

Hi Abba,

Plamens suggestion will work, but it will probably perform worse than
the existing code.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Andrew Morton on 4 Feb 2010 08:45
Abba wrote:
> All Iam trying to do is to reduce the lines of code of some stored
> procedures as the huge number is standing in way of our internal
> audit.

I suggest that changing code to suit the auditing process is the wrong way
to go about it. Would simply removing the new line characters in the code
make the auditing process work better?

--
Andrew


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Optimizer on SQL 2005
Next: Restore using MDF and LDF