How to Assign Cte Value to Variable

how to assign cte value to variable

You can not set values with the SET keyword in the SELECT statement.
You can either assign the fields from the query to variables in the SELECT statement:

WITH CTE AS (
  /** .. Your Query Here .. **/
)
SELECT
  @YourVariable = FieldNameOrSubquery -- In short: Expression
FROM
  CTE

In this case all fields in the SELECT list should be assigned to a variable!

Or you can assign a single row-single column SELECT statement's result to a variable by the SET keyword:

SET @YourVariable = (SELECT COUNT(1) FROM YourTable).

You can not mix the above options.

Furthermore, CTE is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. (http://msdn.microsoft.com/en-us/library/ms175972.aspx). SET is not a SELECT/INSERT/UPDATE/DELETE statement, this is why SQL Server reports a syntax error (CTEs can not be defined in the scope of the SET statement.)

The solution with your example query

;WITH CTEima(PersonId,IsEmployeeActive) AS
( SELECT COUNT(*)
  FROM   custom.viwSSAppsEmpMasterExtended vem
  WHERE  vem.SupervisorPersonId = @p_PersonId

  UNION ALL

  SELECT CTEima.IsEmployeeActive
  FROM   Custom.viwSSAppsEmpMasterExtended vem
  JOIN   CTEima on CTEima.PersonId = vem.SupervisorPersonId
)
SELECT @v_IsManager = COUNT(*)
FROM CTEima
WHERE IsEmployeeActive = 'Y'

How to SET the local variable value equal to result from CTE table?

If you want to get the distinct manager name in that case you don't need CTE.

You are trying to insert multiple values in a VARCHAR data type

DECLARE @mgrname VARCHAR(20)

Instead of creating a VARCHAR variable, you should be creating a TABLE type variable which can hold multiple values like following.

DECLARE @mgrname TABLE(ManagerName VARCHAR(100))

To fetch specific count of distinct manager names you can use

SELECT DISTINCT TOP (@count)

Your final query should look like following.

DECLARE @count INT
DECLARE @mgrname table (ManagerName VARCHAR(100))
SET @count = 3

INSERT INTO @mgrname
SELECT DISTINCT TOP (@count)  Empmanager FROM HR
ORDER BY Empmanager
--Now output is stored inside a table variable.
SELECT * FROM @mgrname

SQL: Set variable during Select inside a CTE

CTE is more like a VIEW rather than a stored procedure. It doesn't seem to be correct syntax - since you can't set a variable inside a CTE - to the best of my knowledge.

Setting a Variable Output with results from Cte's

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

Use variable after common table expression

Move your declaration before the common table expression. Only the statement after the cte can reference the cte. In this case, that was your declare.

declare @K4Resp numeric(5, 2);
with CTE as (
  select 
      case 
        when target_title like '%P1%' then 'P1'
        when target_title like '%P2%' then 'P2'
        when target_title like '%P3%' then 'P3'
        when target_title like '%P4%' then 'P4'
      end as Priority
    , flag
    , case when flag = 'Response' then Business_Hours_MMTR end as Response
    , case when flag = 'Resolution' then Business_Hours_MMTR end as Resolution
  from Keurig..Response_Resolution
  where [Group] like '%Network%'
  and datepart(month, Measurement_Stop) = datepart(month, Getdate()) - 1
  and ( Target_Title like '%P1%'
     or Target_Title like '%P2%'
     or Target_Title like '%P3%'
     or Target_Title like '%P4%'
     )
)
select @K4Resp = CAST(AVG(Response) as numeric(5, 2))
from CTE
where flag = 'Response'
  and Priority = 'P4'
group by 
    Priority
  , flag

---

Related Topics