如何将参数传递到WITH子句Where语句中

Question

如何使用由代码中粗体的with子句中的where语句中的字符串组成的参数。我试过用那种方法，但不管用。

我已经添加了一个名为@param nvarchar(max(的参数。

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    **WHERE SalesPersonID IN ('+ @param +')**
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

Answer 1

您必须使用表变量而不是NVARCHAR，例如：

DECLARE @param TABLE (id int)
INSERT INTO @param VALUES (1), (2), (3)
;WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IN (SELECT id FROM @param)
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

或者，如果@param是逗号分隔的数字列表，则可以使用拆分字符串函数。然后你可以使用IN操作符，如下所示：

WHERE SalesPersonID IN (SELECT id FROM dbo.fnSplitString(@param, ',') 

这里是关于拆分字符串函数的一个很好的参考。

Answer 2

Declare @param varchar(max) = 'some value'
;WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IN (''+ @param +'')
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

或者您可以实现这个动态查询

Answer 3

正如Jonathan所说，您可以在这里使用动态查询，如下所述。

DECLARE @param VARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)
SET @param = '1,2,5'
SET @query  = 'WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
   SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
   FROM Sales.SalesOrderHeader
   WHERE SalesPersonID IN ('+ @param +')
 )
 SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
 FROM Sales_CTE
 GROUP BY SalesYear, SalesPersonID'
 EXEC SP_EXECUTESQL @query

Answer 4

您可以将查询更改为字符串查询，然后执行它。

    DECLARE @SQLQuery VARCHAR(MAX)
    SET @SQLQuery = 'WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IN (' + @param + ')
    )
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
    FROM Sales_CTE
    GROUP BY SalesYear, SalesPersonID'
    EXEC(@SQLQuery)