Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Case Statement Question

Posted on 2007-10-17
Medium Priority
Last Modified: 2008-01-09
I have a query that pulls back a recordset based on a user provided organization code.  If the code is 4 characters long it needs to match one record, if 5 then another, and if 'All' then it just needs to bring back everything.  I'm using the following:

SELECT ROA.Resource_Organization_Assignment_ID, ROA.Five_Dot, RT.Resource_Type_Name, R.Name_Last,
ISNULL(Name_Clarity, R.Name_First) AS First_Name, ROA.Rate_Hourly
FROM t_Resource R, t_Resource_Organization_Assignment ROA, t_Resource_Type RT
WHERE ROA.Resource_ID = R.Resource_ID
AND ROA.Resource_Type_Code = RT.Resource_Type_Code
AND ROA.Resource_Type_Code = 'T'
AND ISNULL(ROA.Date_Start,'01/01/2000') <=  CAST('12/31/' + @Year AS DATETIME)
AND ISNULL(ROA.Date_End, '01/01/2999') >= CAST('01/01/' + @Year AS DATETIME)

CASE WHEN Org_Length = 5 THEN
      AND ROA.Five_Dot = @Org_Code
WHEN Org_Length = 4 THEN
      AND ROA.Four_Dot = @Org_Code

I'm not totally sure this can be done like this but it would help spare me from having the entire query in the SP twice and then mixing in an IF statement.

Thanks in advance,

Question by:stretch73
  • 3
  • 2
LVL 21

Accepted Solution

mastoo earned 1600 total points
ID: 20093867
AND ( Org_Length = 5 And ROA.Five_Dot = @Org_Code
  Or Org_Length = 4 AND ROA.Four_Dot = @Org_Code
  Or Org_Length = not sure how you indicate all here )
LVL 21

Expert Comment

ID: 20093939
If you're keeping a string in Org_Length then

AND ( Org_Length = '5' And ROA.Five_Dot = @Org_Code
  Or Org_Length = '4' AND ROA.Four_Dot = @Org_Code
  Or Org_Length = 'ALL' )

Author Comment

ID: 20094066

That's a good start.  I forgot to include this:


SET @Org_Length = LEN(@Org_Code)

So it's an integer.  
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 20094071
It's bringing back a huge recordset, trying to pin it down.
LVL 19

Assisted Solution

folderol earned 400 total points
ID: 20094206
As a case clause,
where @Org_Code = case len(@Org_Code) when 5 then ROA.Five_Dot when 4 then ROA.Four_Dot else case when @Org_Code = 'All' then @Org_Code end end

however, basing it on mastoo's post is better,

AND(LEN(@Org_Code) = 5 And ROA.Five_Dot = @Org_Code
  Or LEN(@Org_Code) = 4 AND ROA.Four_Dot = @Org_Code
  Or @Org_Code = 'ALL' )

Author Comment

ID: 20094619
Thanks guys, that ended up doing it.  Much simpler than I was making it.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question