vijay

welcome Netizen

Share Your Knowledge.It is a way to achieve immortality

Wednesday, November 1, 2017

How to use Table-Valued Parameters in sql?

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Advantage:

  • Do not acquire locks for the initial population of data from a client.
  • Provide a simple programming model.
  • Enable you to include complex business logic in a single routine.
  • Reduce round trips to the server.
  • Can have a table structure of different cardinality.
  • Are strongly typed.
  • Enable the client to specify sort order and unique keys.
  • Are cached like a temp table when used in a stored procedure. Starting with SQL Server 2012, table-valued parameters are also cached for parameterized queries.
Disadvantage:

  • SQL Server does not maintain statistics on columns of table-valued parameters.
  • Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
  •  Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.
Code:

/* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO

0 comments:

Post a Comment