Must declare the scalar variable

Transact-SQL
1

I am getting:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@PayPeriod".

DECLARE @PayPeriod DATETIME = '2015-10-18 00:00:00',
@EndDate DATETIME = DateAdd(d, 14, @PayPeriod)

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

2

May be separate the variables in 2 declare.

DECLARE  @PayPeriod    DATETIME = '2015-10-18 00:00:00'
DECLARE         @EndDate DATETIME = DateAdd(d, 14, @PayPeriod)

Thanks
Mangal Pardeshi
SQL Master

3

To me it's cleaner to always separate the DECLARE and the SET. That syntax is always consistent:

DECLARE @PayPeriod datetime, @EndDate datetime
SET @PayPeriod = '2015-10-18 00:00:00'
SET @EndDate = DateAdd(d, 14, @PayPeriod)
...rest of code...
4

That worked Thanks, BUT Now I get......

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

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!