Using Case to apply a WHERE Clause - Need help with sytax

Hi all,

I need some help with my syntax for the " Where " part of the following SPROC. I am passing it a INT value and want to set the " where " clause depending on what value I sent it. What is the correct syntax. I have tried the below but it does not work.

Many thanks in advance
-------------------------------------

BEGIN

SELECT st.Student_ID
  , st.Student_Name
  , st.Student_SurNames
From tbl_Students st
 
CASE @StuType
      WHEN 0 THEN
      WHERE IsNull(Enr_Activate_Done,1) = 1
END

CASE @StuType
      WHEN 1 THEN
      WHERE IsNull(Enr_Activate_Done,1) = 1
    AND st.Student_IconImage = 1
END

CASE @StuType
      WHEN 2 THEN
      WHERE IsNull(Enr_Activate_Done,1) = 1
    AND st.Student_IconImage = 2
END
                    
ORDER by st.Student_GivenID

END
LVL 5
Abiel de GrootDeveloperAsked:
Who is Participating?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not this?
SELECT st.Student_ID
   , st.Student_Name
   , st.Student_SurNames
FROM tbl_Students st
WHERE IsNull(Enr_Activate_Done,1) = 1 
    AND (@StuType = 0 OR st.Student_IconImage = @StuType)                     
 ORDER by st.Student_GivenID

Open in new window

0

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
Abiel de GrootDeveloperAuthor Commented:
I really need to modify the WHERE clause depending on the Variable value. I see what you are saying, but is there a way to modify the whole where clause? The above is more of an example, I may need to further vary the Where clause depending on the Int passed

Many thanks Vitor ;-)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
A CASE in the WHERE clause most of the times can be transformed into logic operators so it's better to show us the real case so we can help you better.
Otherwise you'll need to work with SQL dynamic (built the SELECT command and execute it with EXEC() function).
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Abiel de GrootDeveloperAuthor Commented:
Ok, I got so far ...
-------------------

WHERE IsNull(Enr_Activate_Done,1) = 1

AND

  CASE @StuType
  WHEN 0 THEN st.Student_ID > 0 ELSE NULL
  END
, CASE @StuType
  WHEN 1 THEN st.Student_IconImage = 1 ELSE NULL
  END
, CASE @StuType
  WHEN 2 THEN st.Student_IconImage = 2 ELSE NULL
  END
, CASE @StuType
  WHEN 3 THEN st.Student_IconImage = 3 ELSE NULL
  END

                    
ORDER by st.Student_GivenID

END
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, I still keep my above solution to your question but anyway:
  WHERE ((@StuType = 0 AND st.Student_ID > 0) 
   OR st.Student_IconImage = CASE @StuType
           WHEN 1 THEN 1 
           WHEN 2 THEN 2 
           WHEN 3 THEN 3)

Open in new window

As you can see isn't so good to read.
0
Abiel de GrootDeveloperAuthor Commented:
Sorry, you were right!

Many thanks

A
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.