希望传递用户 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;更多推荐
如何在存储过程中将列表作为参数传递?
发布评论