Error when trying to pass comma separated values in SQL Server 2016

Question

I am calling this SQL Server stored procedure from another application in this way:

EXEC GlobalReminder @documentidnumber = '${@documentidnumber}';

The documentidnumber is a variable that contains comma separated values, for example 7568, 8990, 5523. It appears that in this format, I am unable to pass multiple values to this parameter and hence this results in an error.

CREATE PROCEDURE [dbo].[GlobalReminder]
    (@documentidnumber NVARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT SUB, REGION, SORTCODE, CLUSTER 
    FROM TABLE1 ct
    INNER JOIN TABLE2 pl ON ct.TYPE_ID = pl.TYPE_ID
    WHERE DOCUMENT_ID IN (@documentidnumber)
END
GO

Can someone please suggest what would be the simplest way to pass multiple values to a single parameter. I went through some of the existing questions already and the solution mentioned seem to be very complex.

Thanks.

Possible duplicate of [sql server using in keyword pass string array query](https://stackoverflow.com/questions/54418797/sql-server-using-in-keyword-pass-string-array-query) — Andrei Odegov, Feb 03 '19 at 14:18
As stated, your procedure will not work as intended but it will not generate an error. So it seems you have 2 problems to debug. The first is the error (which you did not post) - this will likely reside in your application since the string ${documentidnumber} is nothing special in tsql. Guessing you expect some sort of substitution which can only be functionality within your app. The others have provided corrections to the stored procedure logic. — SMor, Feb 03 '19 at 16:43
Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — GSerg, Feb 03 '19 at 17:18

score 3 · Answer 1 · answered Feb 03 '19 at 14:18

3

You can use split_string():

WHERE DOCUMENT_ID IN (SELECT s.val FROM SPLIT_STRING(@documentidnumber, ',') s(val))

answered Feb 03 '19 at 14:18

Gordon Linoff

1,242,037
58
646
786

Salman A · Accepted Answer · 2019-02-03T17:51:22.297

3

The correct solution is to use table valued parameter. But in SQL Server 2016 you have the STRING_SPLIT function available which you can use inside the stored procedure:

CREATE PROCEDURE [dbo].[GlobalReminder] (
    @documentidnumber nvarchar(max)
) AS
BEGIN
    SET NOCOUNT ON;

    SELECT SUB, REGION, SORTCODE, CLUSTER
    FROM TABLE1 ct
    INNER JOIN TABLE2 pl ON ct.TYPE_ID = pl.TYPE_ID
    WHERE DOCUMENT_ID IN (
        SELECT value
        FROM STRING_SPLIT(@documentidnumber, ',')
    )
END

Note that if DOCUMENT_ID is an integer column then the comma separated list must also consist of valid integers.

edited Feb 03 '19 at 17:51

answered Feb 03 '19 at 14:19

Salman A

262,204
82
430
521

Thanks Salman. Could you please help understand what does 'value' refer to here in your query? Sorry, I am not very good at SQL Server. – Newbie Feb 03 '19 at 14:29
@Newbie It is documented - [string_split](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) – SMor Feb 03 '19 at 16:37
@Newbie this function returns a table with one column called `value`. So `SELECT value FROM STRING_SPLIT('1,2,3,4', ',')` will return a table with 1 column called `value` and 4 rows. – Salman A Feb 03 '19 at 17:38

Error when trying to pass comma separated values in SQL Server 2016

2 Answers2