Multiple-value in one parameter

Question

CREATE FUNCTION [dbo].[func_1]
(
    @ListNum AS nvarchar(MAX)
)
 RETURNS @t TABLE
(
  col_1 nvarchar(MAX)
) 
AS
BEGIN
INSERT @t

SELECT col_1
FROM table_name
WHERE col_2 IN (@ListNum)

RETURN
END

When I pass only one value in paramater (for example : 1) the function correctly works but how can I pass multiple value (for example : 1,2,3,4,5). I get the following error :

Procedure execution failed
42000 - [SQL Server]Error converting data type nvarchar to bigint.

Is there a simple way to solve this?

It won't work because you will get `SELECT col_1 FROM table_name WHERE col_2 IN ('1,2,3,4,5')` and you probably want `SELECT col_1 FROM table_name WHERE col_2 IN (1,2,3,4,5)` — Lukasz Szozda, Sep 01 '15 at 09:28
I forgot to mention that col_2 is a bigint. Where should I put quotes ? — Martin C, Sep 01 '15 at 09:29
possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — shA.t, Sep 01 '15 at 09:29
@MartinC One way is to use Dynamic-SQL like `DECLARE @sql NVARCHAR(MAX) = N'SELECT col_1 FROM table_name WHERE col_2 IN (@ListNum)'; EXEC [dbo].[sp_executesql] @sql, N'@ListNum NVARCHAR(MAX)', @ListNum` **Sorry it won't work no dynamic-sql inside function**!!! — Lukasz Szozda, Sep 01 '15 at 09:42
@MartinC Better use Table-Valued Parameters and `SELECT * FROM table_name t JOIN @tvp tv ON t.col_2 = tv.id` — Lukasz Szozda, Sep 01 '15 at 09:51
possible duplicate of [How to pass an array into a SQL Server stored procedure](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — Panagiotis Kanavos, Sep 01 '15 at 09:52
This question has already been answered many times. In fact, SQL Server allows you to pass table valued parameters so there is no need to pass lists as strings — Panagiotis Kanavos, Sep 01 '15 at 09:58

score 0 · Accepted Answer · answered Sep 01 '15 at 09:45

Hi you can try like this,

CREATE FUNCTION Splitstring (@Input     NVARCHAR(MAX),
                             @Character CHAR(1))
RETURNS @Output TABLE (
  Item NVARCHAR(1000))
AS
  BEGIN
      DECLARE @StartIndex INT,
              @EndIndex   INT

      SET @StartIndex = 1

      IF Substring(@Input, Len(@Input) - 1, Len(@Input)) <> @Character
        BEGIN
            SET @Input = @Input + @Character
        END

      WHILE Charindex(@Character, @Input) > 0
        BEGIN
            SET @EndIndex = Charindex(@Character, @Input)

            INSERT INTO @Output
                        (Item)
            SELECT Substring(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = Substring(@Input, @EndIndex + 1, Len(@Input))
        END

      RETURN
  END

GO

CREATE FUNCTION [dbo].[Func_1] (@ListNum AS NVARCHAR(MAX))
RETURNS @t TABLE (
  col_1 NVARCHAR(MAX))
AS
  BEGIN
      INSERT @t
      SELECT p.col1
      FROM   dbo.Splitstring(@ListNum, ',') s
             JOIN Table_Name t
               ON t.col2 = s.Item

      RETURN
  END

DECLARE @var VARCHAR(100)='1,2,3,4'

SELECT *
FROM   dbo.Func_1(@var)

Introduce one more function called split string. It will return the comma separated list as a table. Join the comma separated table with your actual table. This will gives the result.

This is *not* a good idea. SQL Server already provides table valued parameters. Even if you need to split strings, a loop is the slowest way to do this. Anyway, this question has been answered multiple times already — Panagiotis Kanavos, Sep 01 '15 at 09:57

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

For versions 2008+ using table separated values can assist where the calling procedure can construct the table and you are able to create a table type. If you must pass comma (or other character separated values) in a single string then you will need to separate the comma delimited string into a result set of its own.

The XML method works well when your string doesn't contain any special XML characters such as angle brackets <> - how-to-split-a-comma-separated-value-to-columns

I think this will work for your adjusted function;

CREATE FUNCTION [dbo].[func_1]
(
    @ListNum AS nvarchar(MAX)
)
 RETURNS @t TABLE
(
  col_1 nvarchar(MAX)
) 
AS
BEGIN



DECLARE @S varchar(max),
        @Split char(1),
        @X xml

SELECT @S = @ListNum,
       @Split = ','

SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@S,@Split,'</s> <s>') + '</s>   </root> ')



INSERT @t

SELECT col_1
FROM table_name
WHERE col_2 IN (SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c))

RETURN
END

edited May 23 '17 at 12:06

Community

1
1

answered Sep 01 '15 at 09:56

Edward Comeau

3,874
2
21
24

All current versions of SQL Server support table valued parameters. There is no longer any need to use separated strings – Panagiotis Kanavos Sep 01 '15 at 09:57
This is true, and that response should be marked as the correct answer. It is good to have the various methods of splitting the values for the unfortunate people still working on editions lower than SQL Server 2008. (I'm coding against 2005) – Edward Comeau Sep 01 '15 at 10:05
Coding against an unsupported version isn't fun. Even so, string splitting has been covered exhaustively and there *are* multiple ways to do it, including using CTEs, XML, and the most scaleable solution, SQL CLR. [Aaron Bertrand](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) even published benchmarks of all methods back in 2012 – Panagiotis Kanavos Sep 01 '15 at 10:14

Multiple-value in one parameter

2 Answers2