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

52

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.

This question is tagged with sql sql-server stored-procedures

~ Asked on 2017-02-24 21:27:33

The Best Answer is


60

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;

~ Answered on 2017-02-25 03:42:44


17

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.

~ Answered on 2017-08-09 13:55:27


Most Viewed Questions: