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

Question

Looking to pass a list of User IDs to return a list names. I have a plan to handle the outputed names (with a COALESCE something or other) but trying to find the best way to pass in the list of user IDs. The guts of my sproc will look something like this:

create procedure [dbo].[get_user_names]
@user_id_list, --which would equal a list of incoming ID numbers like (5,44,72,81,126)
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in @user_id_list

Passing the values for @user_id_list is my main concern here.

Answer 1

Adding onto @Matthew Sontum's answer using Table Valued Parameters:

You can INSERT into the user defined type like any other table, and this syntax may be more intuitive for some people.

Type definition:

CREATE TYPE UserList AS TABLE ( UserID INT );

Alternate Method to populate UserList

Example: Populate the UserList Type with all the UserIds of Users with names starting with 'J'

DECLARE @UL UserList;
INSERT INTO @UL 
(
  UserID
)
SELECT UserId FROM dbo.UserTable userTable
WHERE userTable.Username LIKE 'J%'

As per Matthew's answer, you can then call the stored procedure like so:

EXEC dbo.get_user_names @UL, @username OUTPUT;

Check Table Parameter before executing Stored Procedure logic

This was not a requirement for the OP's original question, but we often want to check if the incoming table parameter has values before proceeding in order to avoid any potentially expensive db operations.

Since it is a table, and not a standard variable, it is not feasible to do a NULL check. Instead:

CREATE PROCEDURE [dbo].[get_user_names]
@user_id_list, 
@username varchar (30) output
as
BEGIN 
IF EXISTS (SELECT TOP 1 1 FROM @user_id_list)
BEGIN 
  select last_name+', '+first_name 
  from user_mstr
  where user_id in @user_id_list
END 
--ELSE avoid the expensive operation 

END

Answer 2

this is perfect working for me . this perfect example i hope solved many users problem.

Step 1 Creare reference table in sql like this

 Create TYPE dbo.tblNames
AS TABLE
(
    [Name] nvarchar(max)
);
go

create TYPE dbo.tblNamesWithCols
AS TABLE
(
    [Name] nvarchar(max)
);
go

Step 2 create store procedure with reference table parameters like this

    create proc syTest
    @VarTbleNameList AS dbo.tblNames READONLY,
    @VarTbleNameColsList AS dbo.tblNamesWithCols READONLY,
    @VarWhereQuery nvarchar(max)
    as
    begin
    ......
......  End

**Calling Store Procedure with parameters **

DECLARE @VarTbleList AS dbo.tblNames
INSERT INTO @VarTbleList
VALUES ( 'tblEmployes' )
INSERT INTO @VarTbleList
VALUES ( 'tblDepartments' )
INSERT INTO @VarTbleList
VALUES ( 'tblCities' )

DECLARE @VarTbleColList AS dbo.tblNamesWithCols
INSERT INTO @VarTbleColList
VALUES ( 'tblEmployes.EmployeId as empId;' )
INSERT INTO @VarTbleColList
VALUES ( 'tblEmployes.EmployeName as empName;' )
INSERT INTO @VarTbleColList
VALUES ( 'tblDepartments.DepartmentName as deptName;'  )
INSERT INTO @VarTbleColList
VALUES ( 'tblDepartments.DepartmentId as deptId;' )

EXECUTE  syTest @VarTbleList , @VarTbleColList , @VarWhereQuery ='test'

Answer 3

The proper way is to create a user defined data type:

CREATE TYPE [dbo].[IntArray] AS TABLE
(
    [ID] [INT] NULL
)

Then you can use this custom data type:

CREATE OR ALTER PROCEDURE [dbo].[sp_GetUserNames]
(
    @userIds [IntArray] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        "Name" = u.LastName + ', ' + u.FirstName
    FROM dbo.User u
    JOIN @userIds uid ON u.Id = uid.Id;
END

Usage:

@DECLARE @result TABLE
(
    Name NVARCHAR(max)
);

@DECLARE @ids [IntArray] = SELECT x.userId FROM dbo.sometable x;

SET @result = EXECUTE [dbo].[sp_GetUserNames] @userIds = @ids;

SELECT * FROM @result;

Answer 4

Check the below code this work for me

@ManifestNoList      VARCHAR(MAX)


WHERE
    (
        ManifestNo IN (SELECT value FROM dbo.SplitString(@ManifestNoList, ','))
    )

Answer 5

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

Answer 6

I solved this problem through the following:

1. In C # I built a String variable.

string userId="";

2. 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);

3. 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  
  );

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

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

Answer 7

You can use this simple 'inline' method to construct a string_list_type parameter (works in SQL Server 2014):

declare @p1 dbo.string_list_type
insert into @p1 values(N'myFirstString')
insert into @p1 values(N'mySecondString')

Example use when executing a stored proc:

exec MyStoredProc @MyParam=@p1

Answer 8

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://learn.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://learn.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.

Answer 9

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;

Answer 10

Maybe you could use:

select last_name+', '+first_name 
from user_mstr
where ',' + @user_id_list + ',' like '%,' + convert(nvarchar, user_id) + ',%'

Answer 11

You can try this:

create procedure [dbo].[get_user_names]
    @user_id_list varchar(2000), -- You can use any max length

    @username varchar (30) output
    as
    select last_name+', '+first_name 
    from user_mstr
    where user_id in (Select ID from dbo.SplitString( @user_id_list, ',') )

And here is the user defined function for SplitString:

Create FUNCTION [dbo].[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