Looking for help with a stored procedure!

[Sonya]

Please, please help me !!!!

I have a stored procedure that I need to modify. The stored procedure
is used in an Access DB program where user selects a ProjectID and
views/or can email data for different Units.
I have to make sure that if a projectid is for ‘Fire' Unit report goes
to EVERYONE in a select list and one more user
(v_ddcemployee.employee_id = 2234). If it's not ‘Fire' Unit then
report goes to EVERYONE on select list excluding this specific user.
I have tried case statement and If Else but nothing works – I don't
know how to do this

select ProgUnitName from v_progunit where ProgUnitID = 9 <=== will
return ‘Fire'
Here is my Stored procedure:

CREATE PROCEDURE sp_GetFullReportCCList
@ProjectID varchar (11) AS
SELECT v_progunit.ProgUnitName, tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@ProjectID
GO
I will be forever grateful to everyone that helps me :)
Sonya

[Bruce Loving]

Try this, add the employee_ID to the select, make sure you always get
the employee_id = 2234 and use a derived table.

Select DISTINCT ProgUnitName,SafetyTitleDesc,Name
from ( SELECT v_progunit.ProgUnitName,
tlkpSafetyTitle.SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_DDCemployee
INNER JOIN (tlkpSafetyTitle INNER JOIN (tblSafetyPersnl INNER JOIN
v_progunit ON tblSafetyPersnl.ProgUnitID = v_progunit.ProgUnitID) ON
tlkpSafetyTitle.SafetyTitleID = tblSafetyPersnl.SafetyTitleID) ON
v_DDCemployee.employee_id = tblSafetyPersnl.Employee_ID
WHERE tblSafetyPersnl.SafetyTitleID In (5,6,7,8,10,11,13,15,19)
AND v_progunit.Division='S'
OR tblSafetyPersnl.SafetyTitleID In (5,7,11,13,14,15,19,20)
AND v_progunit.Division='I'

UNION

SELECT v_progunit.ProgUnitName, tlkpProjectTitle.ProjectTitleDesc AS
SafetyTitleDesc,
v_ddcemployee.[Last_Name] + ', ' + v_ddcemployee.[First_Name] AS Name
,v_DDCEmployee.Employee_ID
FROM v_progunit
INNER JOIN (((tblProjectTeam INNER JOIN tlkpProjectTitle ON
tblProjectTeam.ProjectTitleID = tlkpProjectTitle.ProjectTitleID) INNER
JOIN v_tblprojectid ON tblProjectTeam.ProjectID =
v_tblprojectid.ProjectID)
INNER JOIN v_DDCemployee ON tblProjectTeam.loginid =
v_DDCemployee.loginID) ON v_progunit.ProgUnitName =
v_tblprojectid.Unit_Name
WHERE v_progunit.Division='S'
AND v_tblprojectid.ProjectID=@ProjectID
OR v_progunit.Division='I'
AND v_tblprojectid.ProjectID=@ProjectID your union selects here )
where (progUnitName = 'Fire'
UNION
*** select as above where employee_id = 2234, in
other words - always select this employee ***
)
Where ( ProgUnitName = 'Fire') or ( ProgUnitName <> 'Fire' and
employee_id <> 2234)