070-762 Latest Exam Braindumps
Question 16: You manage a database with tables named Invoice and InvoiceDetails. Each invoice may havemultiple 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.
Correct Answer:
Explanation
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:
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?vie
