sql - 如何将 cte 值分配给变量

Question

;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
 )
set @v_IsManager = (select count(*)from CTEima where IsEmployeeActive = 'Y') 

在这里我收到错误
关键字“set”附近的语法不正确

告诉我如何将 CTE 中的值设置为变量

Answer 1

您不能使用 SET 设置值SELECT 中的关键字声明。
您可以将查询中的字段分配给 SELECT 中的变量。声明:

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

在这种情况下，SELECT 列表中的所有字段都应该分配给一个变量!

或者您可以分配单行单列 SELECT语句的结果由 SET 到一个变量关键词:

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

您不能混合使用上述选项。

此外，CTE 定义在单个 SELECT 的执行范围内。 , INSERT , UPDATE , 或 DELETE声明。 ( http://msdn.microsoft.com/en-us/library/ms175972.aspx )。 SET不是 SELECT/ INSERT/ UPDATE/ DELETE语句，这就是 SQL Server 报告语法错误的原因(无法在 SET 语句的范围内定义 CTE。)

示例查询的解决方案

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