select
case
WHEN EXISTS (SELECT
top 1 *
FROM
tbltwrecord r
WHERE
employeeid = 42772
AND statusid IN (
4, 11
)
AND (
(
EndDate >= @PayPeriod
and EndDate <= @EndDate
)
OR (
StartDate >= @PayPeriod
and StartDate <= @EndDate
)
OR (
StartDate <= @PayPeriod
and EndDate >= @EndDate
)
)
ORDER BY
CASE
WHEN r.twscheduleid = 1 THEN 0
WHEN r.twscheduleid = 2 THEN 1
ELSE NULL
END) then 1
else 0
end
The error is clear: you're returning a result set from within a function, and that is not allowed.
You may just need to return the results of that query as the function result. Don't know, since I don't see any function at all. It's impossible for us to accurately attempt to debug what we can't see!