sql - 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

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;