SQL Server 2008 -- Table-Valued Parameter (1)

1.The terms table-valued parameter (TVP) and user-defined table type are used interchangeably.

2.A TVP is based on a new user-defined table type in SQL Server 2008 that describes the schema for a set of rows that can be passed to stored procedures or user-defined functions (UDFs).

--**********Table-Valued Parameters********** --CREATE User-Defined Table Types CREATE TYPE CustomerUdt AS TABLE   (Id int, CustomerName nvarchar(50), PostalCode nvarchar(50)) --DROP User-Defined Table Types DROP TYPE CustomerUdt --Using TVPs for Bulk Inserts and Updates CREATE TYPE LocationUdt AS TABLE(LocationName varchar(50), CostRate int) GO --TVP參數一定要加上READONLY關鍵字，否則會有下列錯誤訊息 --The table-valued parameter "@TVP" must be declared with the READONLY option. CREATE PROCEDURE uspInsertProductionLocation(@TVP LocationUdt READONLY) AS     INSERT INTO [Production].[Location] ([Name],         [CostRate], [Availability], [ModifiedDate])     SELECT *, 0, GETDATE() FROM @TVP GO DECLARE @LocationTVP AS LocationUdt INSERT INTO @LocationTVP SELECT [Name], 0.00 FROM [Person].[StateProvince] EXEC uspInsertProductionLocation @LocationTVP /* --DROP User-Defined Table Types --要先移除PROCEDURE，否則會有錯誤，錯誤訊息如下 --Cannot drop type 'LocationUdt' because it is being referenced     by object 'uspInsertProductionLocation'. --There may be other objects that reference this type. DROP PROCEDURE uspInsertProductionLocation DROP TYPE LocationUdt */