Thanks, changed the following queries:
-- Get list of people who could watch the issue (depends on MODE parameter)
if (@mode = 1)
begin
-- Get all users
select a.userid, a.username, a.firstname + N' ' + a.surname as fullname, a.emailaddress
from users a
left outer join watchissue b on a.userid=b.userid and b.issueid=@issueid
left outer join userroles c on a.userid=c.userid and c.projid=@projid
where a.userid != -1 and b.userid is null and (c.schemeid != 7 or c.userid is null)
order by fullname
end
if (@mode = 2)
begin
-- Get project resources only
select r.userid, a.username, a.firstname + N' ' + a.surname as fullname, a.emailaddress
from users a, projectresource r
left outer join watchissue b on r.userid=b.userid and b.issueid=@issueid
left outer join userroles c on r.userid=c.userid and c.projid=@projid
where r.projid=@projid and r.userid=a.userid and b.userid is null and (c.schemeid != 7 or c.userid is null)
order by fullname
end
if (@mode = 3)
begin
-- Get all users excluding project resources
select a.userid, a.username, a.firstname + N' ' + a.surname as fullname, a.emailaddress
from users a
left outer join watchissue b on a.userid=b.userid and b.issueid=@issueid
left outer join projectresource r on a.userid=r.userid and r.projid=@projid
left outer join userroles c on a.userid=c.userid and c.projid=@projid
where a.userid != -1 and b.userid is null and r.userid is null and (c.schemeid != 7 or c.userid is null)
order by fullname
end
