如何在存储过程中将列表作为参数传递?

本文介绍了如何在存储过程中将列表作为参数传递?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

希望传递用户 ID 列表以返回列表名称.我计划处理输出的名称(使用 COALESCE 或其他)，但试图找到传递用户 ID 列表的最佳方式.我的 sproc 的内容如下所示:

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

传递@user_id_list 的值是我在这里的主要关注点.

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

推荐答案

在 SQL Server 中将值数组传递给存储过程的首选方法是使用表值参数.

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

首先定义类型如下:

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)

最后调用 SP:

EXEC dbo.get_user_names @UL, @username OUTPUT;