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 columnSELECT 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'

Replace your last line with this:

select @v_IsManager = count(*) from CTEima where IsEmployeeActive = 'Y'

;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'

DECLARE @a int
SET @A = 1234
;
WITH CTE
AS (SELECT
  Col1,
  col2
FROM xyz

WHERE Col1 = @A),
dt
AS (SELECT
  @A Col1)

SELECT
  *
FROM dt d
LEFT JOIN CTE vd
  ON vd.col1 = d.col1