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;



No comments:

Post a comment