I have used CASE before, but am currently running into problems using it for
different parameters in the WHERE clause,

CREATE PROCEDURE spTest
@Value int
AS
SELECT *
FROM Category
WHERE CASE WHEN @Value = 1 THEN CatID = 2 ELSE CatID = 3

The above example is very much simplified...

I am getting an error that says, "Error 170: Line 6: Incorrect syntax near
'='.

Anyone know how I can accomplish this?

Thanks,
Drew

Thought I would post the entire SP...

CREATE PROCEDURE spMetricsCollection_NonSupervisors
@EmpSex varchar(1),
@EEOCode varchar(1),
@StartDate datetime,
@EndDate datetime,
@Supervisor int
AS
--Supervisors
SELECT E.EmpID, CAT.Cat, C.CatID, C.CourseTitle, CT.StartDate, CT.EndDate,
E.EmpFName, EmpLName, EmpSex, PosRoleCode
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
P.PosEEOCode = @EEOCode AND CASE WHEN @Supervisor = 1 THEN
(E.PosID = '00003' OR E.PosID = '00009' OR E.PosID = '00013' OR E.PosID =
'00015' OR E.PosID = '00030' OR E.PosID = '00032' OR E.PosID = '00033' OR
E.PosID = '00117' OR E.PosID = '00119' OR E.PosID = '00120' OR E.PosID =
'00121' OR E.PosID = '00122' OR E.PosID = '00131' OR E.PosID = '00141' OR
E.PosID = '00144' OR E.PosID = '00160' OR E.PosID = '00161' OR E.PosID =
'00170' OR E.PosID = '00171' OR E.PosID = '00172' OR E.PosID = '00177' OR
E.PosID = '00178' OR E.PosID = '00179' OR E.PosID = '00186' OR E.PosID =
'00205' OR E.PosID = '00239' OR E.PosID = '00316' OR E.PosID = '00325' OR
E.PosID = '00339' OR E.PosID = '00341' OR E.PosID = '00342' OR E.PosID =
'00343' OR E.PosID = '00345' OR E.PosID = '00352' OR E.PosID = '00357' OR
E.PosID = '00363' OR E.PosID = '00372' OR E.PosID = '00382' OR E.PosID =
'00389' OR E.PosID = '00390' OR E.PosID = '00393' OR E.PosID = '00395' OR
E.PosID = '00397' OR E.PosID = '00398' OR E.PosID = '00401' OR E.PosID =
'00403' OR E.PosID = '00405' OR E.PosID = '00412' OR E.PosID = '00438' OR
E.PosID = '00467' OR E.PosID = '00468' OR E.PosID = '00502' OR E.PosID =
'00506' OR E.PosID = '00509' OR E.PosID = '00514' OR E.PosID = '00616' OR
E.PosID = '00014' OR E.PosID = '00036' OR E.PosID = '00157' OR E.PosID =
'00475' OR E.PosID = '00237' OR E.PosID = '00232' OR E.PosID = '00229' OR
E.PosID = '00233' OR E.PosID = '00383' OR E.PosID = '00040' OR E.PosID =
'00240' OR E.PosID = '00028' OR E.PosID = '00479' OR E.PosID = '00480' OR
E.PosID = '00478' OR E.PosID = '00404' OR E.PosID = '00034')
ELSE
(E.PosID <> '00003' OR E.PosID <> '00009' OR E.PosID <> '00013' OR E.PosID
<> '00015' OR E.PosID <> '00030' OR E.PosID <> '00032' OR E.PosID <> '00033'
OR E.PosID <> '00117' OR E.PosID <> '00119' OR E.PosID <> '00120' OR E.PosID
<> '00121' OR E.PosID <> '00122' OR E.PosID <> '00131' OR E.PosID <> '00141'
OR E.PosID <> '00144' OR E.PosID <> '00160' OR E.PosID <> '00161' OR E.PosID
<> '00170' OR E.PosID <> '00171' OR E.PosID <> '00172' OR E.PosID <> '00177'
OR E.PosID <> '00178' OR E.PosID <> '00179' OR E.PosID <> '00186' OR E.PosID
<> '00205' OR E.PosID <> '00239' OR E.PosID <> '00316' OR E.PosID <> '00325'
OR E.PosID <> '00339' OR E.PosID <> '00341' OR E.PosID <> '00342' OR E.PosID
<> '00343' OR E.PosID <> '00345' OR E.PosID <> '00352' OR E.PosID <> '00357'
OR E.PosID <> '00363' OR E.PosID <> '00372' OR E.PosID <> '00382' OR E.PosID
<> '00389' OR E.PosID <> '00390' OR E.PosID <> '00393' OR E.PosID <> '00395'
OR E.PosID <> '00397' OR E.PosID <> '00398' OR E.PosID <> '00401' OR E.PosID
<> '00403' OR E.PosID <> '00405' OR E.PosID <> '00412' OR E.PosID <> '00438'
OR E.PosID <> '00467' OR E.PosID <> '00468' OR E.PosID <> '00502' OR E.PosID
<> '00506' OR E.PosID <> '00509' OR E.PosID <> '00514' OR E.PosID <> '00616'
OR E.PosID <> '00014' OR E.PosID <> '00036' OR E.PosID <> '00157' OR E.PosID
<> '00475' OR E.PosID <> '00237' OR E.PosID <> '00232' OR E.PosID <> '00229'
OR E.PosID <> '00233' OR E.PosID <> '00383' OR E.PosID <> '00040' OR E.PosID
<> '00240' OR E.PosID <> '00028' OR E.PosID <> '00479' OR E.PosID <> '00480'
OR E.PosID <> '00478' OR E.PosID <> '00404' OR E.PosID <> '00034')
ORDER BY EmpLName
GO

In essence IF @Supervisor = 1 then it will show all supervisors, if not it
will show all non-supervisors...

Thanks,
Drew

Show quoteHide quote

EGADS!  Are you sure this isn't from the dailyWTF?  Whose job is it to check
and update all of these values?  Have you thought of storing them in a table
and doing an inner join????????????????????????????????????????

Once you get beyond one constant to check for, this data REALLY should NOT
be stored in the application or procedure code...



Show quoteHide quote

All of those PosIDs are the supervisor position numbers... I thought it
would be easier to use them then actual employee names, which then of course
I would have to edit when an employee was hired or fired...

Show quoteHide quote

Why aren't those an attribute of the Empoyees or Supervisors table?

That would be worse, true.  But as far as I can see it, I'm not sure why
you're using a database at all.  Part of its purpose is to make this kind of
thing relational, where you don't have to type out supervisor's names or
position numbers in every query.  You say WHERE Employees.Supervisor = 1 or
WHERE Supervisors.EmployeeID = @EmployeeID, etc.

A

There is no supervisors table... they are in the Position table and that is
linked to the Employee table by PosID...

Drew

Show quoteHide quote

I was speaking hypothetically.  Surely you could find a place somewhere to
indicate whether a position ID (whatever the hell that is) or a specific
employee is a supervisor.

Got it... I added a supervisor bit field and set it up...

Thanks for your help...
Drew

Show quoteHide quote

I agree with the 2nd part, but I am just trying to get this data out... I
have started this job and have been upgrading Access databases to SQL
Server... some of the data for the report I am building is coming from
Access, some of it from SQL Server...

I will make a Supervisor column in my Position table, that will make it
easier... I just completely overlooked that solution...

Drew

Show quoteHide quote

Most All of your data element names need a lot of work.

You put prefixes on data element names!!  Think about what VARCHAR(1)
means; are you trying to create extra overhead on purpose? Why does the
same data element keep changing names in each table?  Are you trying to
destroy the Data Dictionary? Do you have only one employee or do you
have the set of Personnel? Etc.

CREATE PROCEDURE CollectNonSupervisorsMetrics
(@sex_codeCHAR(1),
@eeo_code RCHAR(1),
@start_date DATETIME,
@end_date DATETIME,
@supervisor INTEGER)
AS
SELECT P.emp_id, CAT.cat, C.cat_id, C.course_title, CT.start_date,
CT.end_date, P.emp_fname, P.emp_lname, P.sex_code , pos_role_code
  FROM Courses AS C,
       Categories AS CAT,
       ClassesTaught AS CT,
       Training AS T,
       Personnel AS P,
       EmpPersonalInfo AS EP,
       Positions AS P
WHERE CT.class_nbr = T.class_nbr
   AND C.cat_id = CAT.cat_id
   AND C.coursetitle = CT.title
   AND T.emp_nbr = P.emp_nbr
   AND P.emp_id = EP.emp_id
   AND P.pos_id = P.pos_id
   AND EP.sex_code= @sex_code
   AND CT.start_date BETWEEN @start_date AND @end_date
   AND end_date BETWEEN @start_date AND @end_date
   AND P.eeo_code = @eeo_code
   AND CASE
       WHEN @supervisor = 1
            AND P.pos_id IN (<<table of poorly designed codes>>)
       THEN 'T'
       WHEN @supervisor <> 1
            AND P.pos_id NOT IN (<<table of poorly designed codes>>)
       THEN 'T' ELSE 'F' END = 'T';

I have a table with all my position information linked to the employee table
by PosID... there is no supervisor column so I had to figure out all the
supervisor PosID's and use them to filter to db...

Not sure what you mean by the same data element keeps changing names...

Drew

Show quoteHide quote

And what is preventing you from adding one?  Do you really want to "figure
out" and be responsible for constantly updating the codes in all of your
stored procedures?  Or would you rather run a simple UPDATE/INSERT/DELETE
statement when a supervisor is added or removed?

Well, in one table it is called CourseTitle or EmployeeNo, in another it is
called Title or EmpID.  If these things are equivalent in your data model,
then they should have the same name.

C.CourseTitle = CT.Title
EmployeeNo = E.EmpID

Also not sure what CAT.Cat is.

A

All that stuff is temporary stuff between the Imported Access DB and the SQL
Server stuff... I just have to have this report done by Monday and don't
have time to work on upgrading this db really hard at the moment...

Drew

CASE is an expression which returns a value, it is not a control of flow
statement.  The syntax is like this:

SELECT * FROM Cateory WHERE CatID = CASE @Value WHEN 1 THEN 2 ELSE 3 END
-- or
SELECT * FROM Cateory WHERE CatID = CASE WHEN @Value = 1 THEN 2 ELSE 3 END

(Personally, I prefer the latter because the former isn't applicable for ><
ranges or multiple clauses.)




Show quoteHide quote

The CASE expression should end with an...    END.

Show quoteHide quote

Are you the kid that was asleep in my basice of Programming languages
class?  CASE is an **expression** and not a **statement** in SQL.

SELECT *  -- never use * in production code!!!
FROM Category -- never use singular table names; they are sets!
WHERE cat_id =
              CASE  @value = 1
              WHEN 1 THEN  2
               ELSE 3 END;

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.

Show quote Hide quote

Are you the kid that was asleep in 2nd grade spelling class?

That was a simple example that I wrote for sake of the question... as I have
said before, I don't need you to correct my naming conventions, I'm not the
one who set them and I can't change them...

Drew

Show quoteHide quote

He's the kid who got beat up in 2nd grade spelling class and now we all pay
for it.    ;-)

Show quoteHide quote

I can deal as long as I know that he isn't just anal retentive with me...

Drew

Show quoteHide quote

Kinda late to this party, but...

Check out Joe Celko on Amazon; author of 'SQL For Smarties' and a number of
other books on SQL, he's been writing about SQL for longer than many of the
folks here have had hands on keyboards.

My group had some direct interaction with him a few years ago on a sticky
bit of SQL which was interesting enough to get a direct reply.  He was then
and apparently remains.... brusque. 
Tends to be impatient with what he thinks are RTFM-type questions.  'tends
to be' in this sense is like saying 'some products require assembly' when
buying from IKEA.



Show quoteHide quote

Also, if I wanted to be anal retentive as you are,

SELECT *  -- never use * in production code!!!
FROM Category -- never use singular table names; they are sets!
WHERE cat_id =
              CASE WHEN @value = 1
              THEN 1 THEN  2
               ELSE 3 END;

Drew


Show quoteHide quote

CREATE PROCEDURE spTest
@value int = 1
AS
SELECT *
FROM Categories
WHERE CategoryID =CASE WHEN @Value = 1
            THEN 2
                  ELSE  3
                  end

exec spTest
drop PROCEDURE spTest

Use Northwind DB  to view and test.

Show quoteHide quote

Change your WHERE clause to read:

WHERE CatID = CASE WHEN @Value = 1 THEN 2 ELSE 3

That should do the trick.

-kw

Show quoteHide quote

Tempdb is growing and I can't tell what process is to blame
Beating a dead horse - Drop table from proc
Can I do this?
Stored Procedure (Weird Question)
Basic Query Help