SQL Server Table Type Or Table-Valued Parameters

• SQL Server 2008 introduced the concepts of User-Defined Table Types (UDTT) and Table-Valued Parameters (TVP)
• These help you to pass a Data table as a parameter.
• You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement such as a stored procedure or function, without creating a temporary table or many parameters.
• It will help full to executing inner procedure without looping or you can directly pass table data to procedure

/* Create a table type. */

CREATE TYPE SampleTableType AS TABLE

(

Name VARCHAR(50),

Age INT

)

/* Create a procedure to receive data for the table-valued parameter. */

CREATE PROCEDURE dbo. SampleProcTableType

@Temptable SampleTableType READONLY

AS

SET NOCOUNT ON

BEGIN

-- INSERT INTO StudentTable /*You Can add insert command to database*/

SELECT

Name, Age

FROM @Temptable

END

/* Declare a variable that references the type.to execute proc */

DECLARE @SampleTemp AS SampleTableType

/* 

Add data to the table variable. 

Pass the table variable data to a stored procedure and execute. 

*/

INSERT INTO @SampleTemp (Name, Age)

SELECT 'Manu',20

UNION ALL

SELECT 'Raj',25

UNION ALL

SELECT 'Arun',25

EXEC SampleProcTableType @SampleTemp;