How to pass parameter into WITH clause Where Statement?

Question

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

You have 2 choices, 1. Use dynamic SQL, 2, Use LIKE with the known pattern of @param — Eric, Oct 02 '15 at 05:55
How do I do it with dynamic sql? I tried using SET @sql = 'Select.......' inside the with clause but I don't think it really works. can you show me how to do it your way using dynamic sql? — James Boer, Oct 02 '15 at 05:57
Ideally, change your calling convention. Use xml or, ideally, a table-valued parameter, rather than a *string*. Both of the former types are *designed* for holding multiple values, and can be used (relatively) naturally in a query. A string (that I'm guessing is going to be a string containing commas and possible quotation marks) is going to be treated as a single string, here as in most other languages. — Damien_The_Unbeliever, Oct 02 '15 at 05:59
If @param is like '1,2,3' or '1', you can easily create the where condition without dynamic SQL `WHERE ',' + @param + ',' LIKE '%,' + CAST(SalesPersonID as varchar(10)) + ',%'` — Eric, Oct 02 '15 at 06:00
the @param will be like "'ID 21312','ID 1243213','ID 342342'......." something like that — James Boer, Oct 02 '15 at 06:16

Giorgos Betsos · Answer 1 · 2015-10-02T06:10:51.537

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.

score 0 · Answer 2 · answered Oct 02 '15 at 05:56

0

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

answered Oct 02 '15 at 05:56

mohan111

8,633
4
28
55

Hi, thanks for the reply. But I do receive error: Conversion failed when converting date and/or time from character string error during data adapter.fill(dataset) line as the parameter consists of a string . – James Boer Oct 02 '15 at 06:11

japzdivino · Answer 3 · 2015-10-02T06:53:56.890

0

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)

edited Oct 02 '15 at 06:53

answered Oct 02 '15 at 05:57

japzdivino

1,736
3
17
25

Hi, it does not work thanks as the parameter consists a string. Conversion failed when converting date and/or time from character string error during data adapter.fill(dataset) line. – James Boer Oct 02 '15 at 06:13
@newtoasp i modified my answer into string sql query, i think that is the better way to achieve what you want. – japzdivino Oct 02 '15 at 06:54
I have an error with Incorrect syntax near the keyword 'WITH'. Please advice thanks – James Boer Oct 02 '15 at 07:17

score 0 · Answer 4 · answered Oct 02 '15 at 06:12

0

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

answered Oct 02 '15 at 06:12

J-D

1,575
1
11
22

My main concern now is that my WITH clause is rather long that exceeds 4000character if im putting the whole statement into it. Please advice. – James Boer Oct 02 '15 at 06:19
The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage. So in your case if the parameter size is much larger then you also have to declare param as a NVARCHAR(max). – J-D Oct 02 '15 at 06:23
@J-D - a `nvarchar(max)` *variable* can store [more than 2GB](http://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable). The 2GB limit applies to *columns*. – Damien_The_Unbeliever Oct 02 '15 at 06:25
isn't it restricted by the number of characters in the SET @QUERY = ' '? it is by size? – James Boer Oct 02 '15 at 06:25
@newtoasp - not *as such*. You have to be careful when [concatenating multiple strings together though](https://msdn.microsoft.com/en-GB/library/ms177561.aspx): "If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur." – Damien_The_Unbeliever Oct 02 '15 at 06:27
just have a look at this stack question http://stackoverflow.com/questions/3190423/how-do-i-execute-a-very-long-dynamic-sql-statement – J-D Oct 02 '15 at 06:32
I have an error with Incorrect syntax near the keyword 'WITH'. Please advice thanks – James Boer Oct 02 '15 at 06:57
Thanks for the above notes on NVARCHAR – James Boer Oct 02 '15 at 06:58
This will help you in your case http://stackoverflow.com/questions/7032425/incorrect-syntax-near-the-keyword-with – J-D Oct 02 '15 at 07:00
adding a ; does not work for me using dynamic sql tho – James Boer Oct 02 '15 at 07:14

How to pass parameter into WITH clause Where Statement?

4 Answers4