CODEWITHSUNDEEP
sqlsql-server-2008
James Boer
James Boer

Reputation: 321

How to pass parameter into WITH clause Where Statement?

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 doesn't 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

Upvotes: 1

Views: 15399

Answers (4)

japzdivino
japzdivino

Reputation: 1746

You can change the query to string query, then execute it.

    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)

Upvotes: 0

J-D
J-D

Reputation: 1575

As stated by Jonathan, you can use dynamic query here as stated below.

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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You have to use a table variable instead of a NVARCHAR, e.g.:

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

Alternatively, if @param is a comma separated list of numbers you can use a split string function. Then you could use IN operator like this:

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

Here is a very good reference on split string functions.

Upvotes: 2

mohan111
mohan111

Reputation: 8865

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

or you can achieve this Dynamic query

Upvotes: 0

Related Questions