Create and alter stored procedures - Table Valued Parameter

Table Valued Parameters introduced in SQL Server 2008. We can use table-valued parameters to send multiple rows of data to a stored procedure or function, without creating a temporary table. Now we can pass whole table as a parameter, no need to use XML data type and passing XML data and OPENXML. Table valued parameters are declared by using user-defined table types.


Some facts and feature about Table Valued parameter.

• Data in the table variable can not be modified. Must be passed as READONLY.
• Can not use as a OUTPUT parameter. We can only use table variables as input parameters.
• No statistics has been save about that.

Here I have created a example to use table valued parameter. Objective of this ex is to insert data into table via stored procedure. Before 2008 we used bulk insert through XML parsing but now pass multiple rows into procedure through table valued parameter.

--Creatae table
CREATE TABLE [dbo].[Products]
  (        
        [ProductID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,        
        [ProductName] [varchar](100) NULL,              
        [CreateDate] [datetime] NULL,              
        [SalePrice] [money] NULL
  )
  GO

--Check created table
select * from dbo.[Products]  


--Create Table type, we can create user define table type
Create Type ProductTableType As Table
(
[ProductName] [varchar](100) NULL,              
    [CreateDate] [datetime] NULL,              
    [SalePrice] [money] NULL
)
Go


-- We can check latest created table type here
select * from sys.table_types


-- Create Stored procedure to pass table type
CREATE PROCEDURE sp_InsertProduct
 (
       @TableVar ProductTableType READONLY
 )
 AS
 BEGIN
       INSERT INTO [Products](ProductName, CreateDate, SalePrice)
       SELECT ProductName,CreateDate,SalePrice
       FROM @TableVar
       WHERE SalePrice < 1000  
 END
 GO


-- Insert value into table throgh Table value type
Declare @ProductType as dbo.ProductTableType
insert into @ProductType(ProductName,CreateDate,SalePrice) values ('Dant Kanti',getdate(),'50.00')
insert into @ProductType(ProductName,CreateDate,SalePrice) values ('Amla Ras',getdate(),'150.50')
insert into @ProductType(ProductName,CreateDate,SalePrice) values ('Kesh Kanti',getdate(),'70.00')
insert into @ProductType(ProductName,CreateDate,SalePrice) values ('Aelobera',getdate(),'200.00')
insert into @ProductType(ProductName,CreateDate,SalePrice) values ('Aarogya Aata',getdate(),'170.00')


--select * from @ProductType


Exec dbo.sp_InsertProduct @ProductType
Go


-- Here we can check inserted value into table
Select * from dbo.Products


Suggestions are always welcome.


Thanks.