You need to create a stored procedure that accepts a table-valued parameter named @Customers. Which code segment should you use?

Question

Options

• ACREATE PROCEDURE AddCustomers (@Customers varchar(max))
• BCREATE PROCEDURE AddCustomers (@Customers Customer READONLY)
• CCREATE PROCEDURE AddCustomers (@Customers CustomerType OUTPUT)
• DCREATE PROCEDURE ADDCUSTOMERS

• A
  5% (1)
• B
  79% (15)
• C
  5% (1)
• D
  11% (2)

Explanation

To create and use table-valued parameters, follow these steps: 1. Create a table type and define the table structure. /* Create a table type. / CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); 2. Declare a routine that has a parameter of the table type. / Create a procedure to receive data for the table-valued parameter. CREATE PROCEDURE usp_InsertProductionLocation @TVP LocationTableType READONLY INSERT INTO [AdventureWorks2008R2].[Production].[Location] ,[ModifiedDate]) SELECT , 0, GETDATE() 3. Declare a variable of the table type, and reference the table type. / Declare a variable that references the type. / DECLARE @LocationTVP AS LocationTableType; 4. Fill the table variable by using an INSERT statement. / Add data to the table variable. / INSERT INTO @LocationTVP (LocationName, CostRate) SELECT [Name], 0.00 [AdventureWorks2008R2].[Person].[StateProvince]; 5. After the table variable is created and filled, you can pass the variable to a routine. / Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; Table-valued parameters must be passed as input READONLY parameters to Transact- SQL You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

No community discussion yet for this question.