Your question isn't completely clear, but I'm guessing you want something like this:
SELECT
'Employee Id' = EE.StaffID,
'Name' = RTRIM(LTRIM(EE.Name))
FROM
Employee As EE
WHERE
(
@Staff_Type = 'Casual'
And
Exists
(
SELECT 1
FROM CasualStaff As CS
WHERE CS.StaffID = EE.StaffID
)
)
Or
(
@Staff_Type = 'Permanent'
And
Not Exists
(
SELECT 1
FROM CasualStaff As CS
WHERE CS.StaffID = EE.StaffID
)
)
NB: It would probably be simpler to have a flag on the
Employee
table to indicate the type of employee; but that depends on what else you're using the
CasualStaff
table for.