SQLTeam.com | Weblogs | Forums

Select statements included within a function cannot return data to a client


#1

I get this error. How do I do this:
Select statements included within a function cannot return data to a client.

CREATE FUNCTION [getTWHasTeleworkAgreement]
(
@EmployeeId int
,@PayPeriod smalldatetime
)
RETURNS int
AS
BEGIN
DECLARE @agree int,
@EndDate DATETIME = DateAdd(d, 14, @PayPeriod)
select
case
WHEN EXISTS (SELECT
top 1 *
FROM
tbltwrecord r
WHERE
employeeid = @EmployeeId
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 set @agree = 1
else set @agree =0
end
RETURN @agree
END


#2
SELECT CASE ... THEN SET ...

Is not valid SQL. However, you can say:

SELECT @var = CASE 
    WHEN ... then 1
    ELSE 2
END