Table-valued parameters
You manage a database with tables named Invoice and InvoiceDetails. Each invoice may have multiple records.
Users update the InvoiceDetails table by using a .NET web application. The application retrieves records from both tables and updates the tables by running an inline update statement.
Users experience slow performance when updating records in the application. The solution must meet the following requirements:
✑ Must use a stored procedure. ✑ Must not use inline update statements ✑ Must use a table-valued parameter. ✑ Must call the stored procedure to update all records.
You need to optimize performance.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer given:
Box 1: Create a user-defined table type"¦ 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.
Box 2: ..read-only input parameter. Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines.
Box 3:Send invoice detail records to the sorted procedure as a ADO.NET table for the input parameter Example - The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.
USE AdventureWorks2012;
/* 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 Etc.¨
/* 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 -
References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017