CODEWITHSUNDEEP
sql-servervariablesoutputcommon-table-expression
dori2o
dori2o

Reputation: 31

Setting a Variable Output with results from Cte's

I'm trying to create a stored procedure in which I want to set a Variable Output value by using a Select Statement from which the information is provided by CTE's:

Here's my code:

Create procedure [dbo].[GetAvailableTime]
@ID int,
@week int,
@DirectTime int output

as

;with 

cte_stg1
as
(
select
tim.[TimesheetID] as [TimeSheet Tim]
,sum(st1.Mins) as [S1Mins]
from [dbo].[Timesheets] as tim
left join [dbo].[Stage1] as st1 on st1.[TimesheetID] = tim.[TimesheetID]
where st1.[TimesheetID] = tim.[TimesheetID]
group by tim.[TimesheetID]
),
--------------
cte_stg1l
as
(
select
tim.[TimesheetID] as [TimeSheet Tim]
,sum(s1l.[Mins]) as [S1LMins]
from [dbo].[Timesheets] as tim
left join [dbo].[Stage1Leave] as s1L on s1l.[TimesheetID] = tim.[TimesheetID]
where s1L.[TimesheetID] = tim.[TimesheetID]
group by tim.[TimesheetID]
)

set @DirectTime = (select Isnull(Sum(isnull(E.[WeeklyContractedHours],0) - isnull(cte_stg1l.[S1LMins],0) - isnull(cte_stg1.[S1Mins],0)),0)
                    from [dbo].[Employees] as E 
                    left join [dbo].[Timesheets] as tim on E.[EmployeeProfileID] = tim.[EmployeeID]
                    left join cte_stg1 as cte_stg1 on cte_stg1.[TimeSheet Tim] =tim.[TimesheetID]
                    left join cte_stg1l as cte_stg1l on cte_stg1l.[TimeSheet Tim] =tim.[TimesheetID]
                    where 
                    E.[EmployeePID] = @PID and
                    tim.[WeekID] = @week)

The result should be an integer value which represents minutes.

@DirectTime 
===========
1530

However, I'm getting the error Incorrect syntax near the keyword 'set'.

Can anyone advise what I'm doing wrong, and if possible suggest how I might overcome this issue.

Thanks

Upvotes: 0

Views: 290

Answers (1)

user5386938
user5386938

Reputation:

Try replacing set @DirectTime = (select Isnull(Sum... with select @DirectTime = Isnull(Sum... and remove the trailing closing parenthesis.

select @DirectTime = Isnull(Sum(isnull(E.[WeeklyContractedHours],0) - isnull(cte_stg1l.[S1LMins],0) - isnull(cte_stg1.[S1Mins],0)),0)
                    from [dbo].[Employees] as E 
                    left join [dbo].[Timesheets] as tim on E.[EmployeeProfileID] = tim.[EmployeeID]
                    left join cte_stg1 as cte_stg1 on cte_stg1.[TimeSheet Tim] =tim.[TimesheetID]
                    left join cte_stg1l as cte_stg1l on cte_stg1l.[TimeSheet Tim] =tim.[TimesheetID]
                    where 
                    E.[EmployeePID] = @PID and
                    tim.[WeekID] = @week

Upvotes: 1

Related Questions