推荐订阅源

表值参数 （新特点）

USE Blog
GO
--创建演示表

CREATE TABLE Employee
(
    ID int,
    Name nvarchar(20),
    Gender char(1),
CONSTRAINT PK_Employee PRIMARY KEY (ID)
)
GO

--创建演示存储过程
CREATE PROCEDURE sp_NewEmployee
    @id int,
    @name nvarchar(20),
    @gender char(1)
AS
INSERT INTO Employee VALUES (@id, @name, @gender)
GO
--创建自定义表类型
CREATE TYPE EmployeeType AS TABLE
    (ID int, Name nvarchar(20), Gender char(1))
GO

--创建批量插入存储过程
CREATE PROCEDURE sp_NewEmployee_Batch
    (@employees EmployeeType READONLY)--只读的Employee表类型
AS
INSERT INTO Employee SELECT * FROM @employees
GO

--单行操作
EXEC sp_NewEmployee 5, '张三', 'm'

--多行操作
DECLARE @emps EmployeeType
INSERT INTO @emps VALUES (6, '李四', 'f')
INSERT INTO @emps VALUES (7, '王五', 'm')
INSERT INTO @emps VALUES (8, '候六', 'm')

EXEC sp_NewEmployee_Batch @emps

--查询
SELECT * FROM Employee