如何将数字数组传递给存储过程? [英] How to pass an array of numbers to a stored procedure?

问题描述

我想编写一个可以接收一个或多个员工 ID 并根据这些 ID 返回数据的存储过程.每次调用 sproc 时，员工 ID 的数量都会有所不同.

I would like to write a stored procedure that can receive one or more employee IDs and return data based on these IDs. The number of employee IDs will vary every time the sproc is called.

如何编写存储过程来返回传入的每个员工 ID 的分支地址，其中员工 ID 的数量是可变的?

How can I write a the stored procedure to return the Branch Address of every employee ID passed in, where the number of employee IDs will be variable?

• 身份证
• 姓名
• Branch_ID

• 身份证
• 姓名
• 地址

推荐答案

如果您运行的是 SQL 2008 或更高版本，那么我建议您使用表类型.然后您可以将表传递给存储过程.这是我制作的一个小例子来证明这一点.

If you have an SQL 2008 or higher running then I'd suggest using table types. Then you can pass a table to the stored procedure. Here is a little example I made to demonstrate just that.

create database tmpDB;
go

use tmpDB;
go

create table tblEmployee
    (
     ID int identity(1, 1)
            primary key
            not null,
     Name nvarchar(200),
     Branch_ID int
    );
go    

insert  into dbo.tblEmployee
        (Name, Branch_ID)
values  (N'Brian', 1),
        (N'Mary', 1),
        (N'Peter', 2),
        (N'Sonya', 2),
        (N'Roland', 1),
        (N'Tom', 3),
        (N'Sam', 3),
        (N'Ryan', 3),
        (N'Julia', 3),
        (N'Tim', 1),
        (N'Eva', 2); 
go        


select  *
from    dbo.tblEmployee;
go

create type typEmployee as table
(
Name nvarchar(200),
BranchID int
);
go

grant exec on type::typEmployee to public;
go

create procedure spInsertEmployees
    (
     @NewEmployees typEmployee readonly
    )
as
    begin
        merge tblEmployee as target
        using
            (
             select Name,
                    BranchID
             from   @NewEmployees
            ) as source (Name, BranchID)
        on (
            target.Name = source.Name
            and target.Branch_ID = source.BranchID
           )
        when not matched then
            insert (Name, Branch_ID)
            values (
                    source.Name,
                    source.BranchID
                   );
    end;
go

grant execute on spInsertEmployees to public;
go

declare @tmpTable typEmployee;
insert  into @tmpTable
        (Name, BranchID)
values  (N'NewEmployee', 1),
        (N'NewEmployee', 2),
        (N'NewEmployee', 3),
        (N'Sonya', 2),
        (N'Tim', 1);

exec dbo.spInsertEmployees
    @NewEmployees = @tmpTable;


select  *
from    dbo.tblEmployee;
go

use master;
go

drop database tmpDB;
go

这篇关于如何将数字数组传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！