如何将 cte 值分配给变量 [英] how to assign cte value to variable

问题描述

;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"附近的语法不正确

here i am getting error like Incorrect syntax near the keyword 'set'

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

tell me how to set values from CTE into variable

推荐答案

您不能在 SELECT 语句中使用 SET 关键字设置值.您可以将查询中的字段分配给 SELECT 语句中的变量:

You can not set values with the SET keyword in the SELECT statement. You could 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

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

或者你可以通过SET关键字将单行单列 SELECT语句的结果赋值给一个变量:

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).

以上选项不能混用.

此外，CTE 定义在单个 SELECT、INSERT、UPDATE 或 DELETE 的执行范围内> 声明.(http://msdn.microsoft.com/en-us/library/ms175972.aspx).SET 不是 SELECT/INSERT/UPDATE/DELETE 语句，这是为什么SQL Server报语法错误(在SET语句的范围内不能定义CTE.)

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.)

示例查询的解决方案

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

这篇关于如何将 cte 值分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！