How do I pass a list as a parameter in a stored procedure?

The preferred method for passing an array of values to a stored procedure in SQL server is to use table valued parameters.

First you define the type like this:

CREATE TYPE UserList AS TABLE ( UserID INT );

Then you use that type in the stored procedure:

create procedure [dbo].[get_user_names]
@user_id_list UserList READONLY,
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in (SELECT UserID FROM @user_id_list)

So before you call that stored procedure, you fill a table variable:

DECLARE @UL UserList;
INSERT @UL VALUES (5),(44),(72),(81),(126)

And finally call the SP:

EXEC dbo.get_user_names @UL, @username OUTPUT;

As far as I can tell, there are three main contenders: Table-Valued Parameters, delimited list string, and JSON string.

Since 2016, you can use the built-in STRING_SPLIT if you want the delimited route: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

That would probably be the easiest/most straightforward/simple approach.

Also since 2016, JSON can be passed as a nvarchar and used with OPENJSON: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

That's probably best if you have a more structured data set to pass that may be significantly variable in its schema.

TVPs, it seems, used to be the canonical way to pass more structured parameters, and they are still good if you need that structure, explicitness, and basic value/type checking. They can be a little more cumbersome on the consumer side, though. If you don't have 2016+, this is probably the default/best option.

I think it's a trade off between any of these concrete considerations as well as your preference for being explicit about the structure of your params, meaning even if you have 2016+, you may prefer to explicitly state the type/schema of the parameter rather than pass a string and parse it somehow.

Azure DB, Azure Data WH and from SQL Server 2016, you can use STRING_SPLIT to achieve a similar result to what was described by @sparrow.

Recycling code from @sparrow

WHERE user_id IN (SELECT value FROM STRING_SPLIT( @user_id_list, ',')

Simple and effective way of accepting a list of values into a Stored Procedure

I solved this problem through the following:

In C # I built a String variable.

string userId="";

I put my list's item in this variable. I separated the ','.

for example: in C#
userId= "5,44,72,81,126";

and Send to SQL-Server

 SqlParameter param = cmd.Parameters.AddWithValue("@user_id_list",userId);

I Create Separated Function in SQL-server For Convert my Received List (that it's type is NVARCHAR(Max)) to Table.

CREATE FUNCTION dbo.SplitInts  
(  
   @List      VARCHAR(MAX),  
   @Delimiter VARCHAR(255)  
)  
RETURNS TABLE  
AS  
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM  
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')  
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'  
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')  
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y  
      WHERE Item IS NOT NULL  
  );

In the main Store Procedure, using the command below, I use the entry list.

SELECT user_id = Item FROM dbo.SplitInts(@user_id_list, ',');

How do I pass a list as a parameter in a stored procedure?

Related

Recent Posts