表值参数 （新特点）
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
