asked on

SQL Case Statement Question

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
END

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,

N

ASKER CERTIFIED SOLUTION

mastoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mastoo

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' )

stretch73

ASKER

Mastoo,

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

DECLARE @Org_Length INT

SET @Org_Length = LEN(@Org_Code)

So it's an integer.

stretch73

ASKER

It's bringing back a huge recordset, trying to pin it down.

SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stretch73

ASKER

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