T-SQL query help

Need help on T-SQL Where using CASE below has error:

Select ...
From ...
Where
...
CASE
WHEN (@Site = 'D' AND ISDATE(@BDATE) = 1 AND ISDATE(@EDATE) = 1)            
THEN
BEGIN
AND TYPE = 'RD'  
AND CONVERT(VARCHAR(10), DATETIME, 101) >= @BDATEF
AND CONVERT(VARCHAR(10), DATETIME, 101) <= @EDATE
END
WHEN @Site = 'S'
THEN
BEGIN
AND TYPE = 'STD'
END
END CASE
jfreeman2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Lose all of the 'BEGIN' and 'END' blocks and run again, and if it still fails give us an exact error message.
BEGIN..END causes the code within to execute as a single block, and afaik that is not allowed within a CASE statement.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think you have all the logic wrong.

You should have something similar to:
Select ...
 From ...
 Where 
 ...
(
  (@Site = 'D' AND ISDATE(@BDATE) = 1 AND ISDATE(@EDATE) = 1
AND TYPE = 'RD'  AND CONVERT(VARCHAR(10), DATETIME, 101) >= @BDATEF
AND CONVERT(VARCHAR(10), DATETIME, 101) <= @EDATE)
OR 
(@Site = 'S'  AND TYPE = 'STD') 
)

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Also, CASE blocks in a WHERE clause is not considered best practice, not sure why, so try this:
WHERE (
   @Site = 'D' 
   AND ISDATE(@BDATE) = 1
   AND ISDATE(@EDATE) = 1 
   AND TYPE = 'RD'  
   AND CONVERT(VARCHAR(10), DATETIME, 101) >= @BDATEF
   AND CONVERT(VARCHAR(10), DATETIME, 101) <= @EDATE
) OR (
   @Site = 'S' 
   AND TYPE = 'STD') 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jfreeman2010Author Commented:
Thank you lim Horn for reply.

After remove begin.. end.

Now error on CASE ... END

The last statement before CASE is like AND TestNum is null
jfreeman2010Author Commented:
@Site = 'S'  is a condition to have "AND TYPE = 'STD' " in where cause. Same is when
@Site = 'D'
   AND ISDATE(@BDATE) = 1
   AND ISDATE(@EDATE) = 1
Then
   AND TYPE = 'RD'  
   AND CONVERT(VARCHAR(10), DATETIME, 101) >= @BDATEF
   AND CONVERT(VARCHAR(10), DATETIME, 101) <= @EDATE
jfreeman2010Author Commented:
lim Horn & Vitor Montalvao, I am trying to understand both of your suggestion....
jfreeman2010Author Commented:
Thank you !!
Scott PletcherSenior DBACommented:
You absolutely do not want to convert the datetime column in the table to varchar; that's not only extremely slow, it will give you wrong results.

My best guess it that you are looking for a structure like this:


Select ...
from ...
Where
1 =
CASE
WHEN (@Site = 'D' AND ISDATE(@BDATE) = 1 AND ISDATE(@EDATE) = 1)            
THEN CASE WHEN TYPE = 'RD' AND [DATETIME] >= @BDATE AND [DATETIME] <= @EDATE THEN 1 ELSE 0 END
WHEN @Site = 'S'
THEN CASE WHEN TYPE = 'STD' THEN 1 ELSE 0 END
ELSE 0
END
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.