用户定义表类型：
CREATE TYPE [dbo].[TVP_Location] AS TABLE(
[Location] [varchar](50) NOT NULL,
[Address] [varchar](4) NULL,
[WorkUnit] [int] NULL,
[ItemCode] [varchar](100) NULL,
[ItemName] [nvarchar](500) NULL,
[Row] [int] NULL,
[Column] [int] NULL,
[LampAddress] [varchar](4) NULL
)
GO
表类型的使用：
CREATE PROCEDURE [dbo].[P_Location]
@tvpLocation TVP_Location Readonly
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
begin transaction TRAN1
declare @errors int
delete from L_Location -- 删除已有库位
set @errors = @@ERROR;
insert into L_Location([Location],[Address],[WorkUnit],[ItemCode],[ItemName],[Row],[Column],[LampAddress])
select [Location],[Address],[WorkUnit],[ItemCode],[ItemName],[Row],[Column],[LampAddress]
from @tvpLocation
set @errors = @errors + @@ERROR;
if (@errors<>0)
begin
rollback transaction TRAN1
end
else
begin
commit transaction TRAN1
end
END