如何将参数传递给WITH子句Where Statement？ [英] How to pass parameter into WITH clause Where Statement?

问题描述

如何在代码中以粗体显示的with子句中的where语句中使用包含字符串的参数。我尝试过这种方法但它不起作用。



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

< br $>

How do I use a parameter that consist a string in a where statement within a with clause which is bold in the codes. I tried doing that method but its does not work.

I have added a parameter called @param nvarchar(max) already.

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

推荐答案

您可以像使用传统语句一样在WITH子句中使用参数。示例中的问题是需要一个值列表的IN运算符。



看看这个提示如何在IN中使用参数：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

You can use parameters in a WITH clause just like in a traditional statement. The problem in the example is the IN operator which requires a list of values.

Have a look at this tip how you can use a parameter with IN: Using comma separated value parameter strings in SQL IN clauses[^]

你可以使用动态sql并执行它

you can use dynamic sql and execute it

DECLARE @SQLQuery AS NVARCHAR(MAX)
SET @SQLQuery = 'SELECT * FROM table1 WHERE EmployeeID in(' + @param +')'
EXECUTE(@SQLQuery)

这篇关于如何将参数传递给WITH子句Where Statement？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！