You administer a Microsoft SQL Server 2008 database that contains a table named dbo.[order]. There are no triggers on the table. You plan to create a stored procedure that will have the following parameters:
•@ProdId int
•@CustId int
You need to ensure that the following requirements are met:
•The OrderID and ProdID values of each modified row are captured into a local table variable before data is modified.
•The ProdID is modified to @ProdID where CustID is equal to @CustId.
Which Transact-SQL statement should you use?
A. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID =@ProdId
OUTPUT DELETED.OrderID, DELETED.ProdID
INTO @OrderIDs WHERE CustID = @CustId;
B. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID = @ProdId
WHERE CustID = @CustId;
INSERT into @OrderIDs
SELECT SCOPE_IDENTITY(OrderId), SCOPE_IDENTITY(ProdId);
C. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID = @ProdId
OUTPUT INSERTED.OrderID, INSERTED.ProdID
WHERE CustID = @CustId;
D. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID = @ProdId
OUTPUT INSERTED.OrderID, INSERTED.ProdID
INTO @OrderIDs
WHERE CustID = @CustId;
E. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID = @ProdId
OUTPUT SELECT d.OrderID, d.ProdID
FROM DELETED d
INTO @OrderIDs
WHERE CustID = @CustId;
F. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE dbo.[order]
SET ProdID = @ProdId
OUTPUT INSERTED.OrderID, DELETED.ProdID
INTO @OrderIDs WHERE CustID = @CustId;
G. UPDATE [order]
SET ProdID = @ProdId
OUTPUT DELETED.OrderID, DELETED.ProdID
INTO @OrderIDs (OrderID INT, ProdID INT)
WHERE CustID = @CustId;
H. DECLARE @OrderIDs TABLE (OrderID INT, ProdID INT);
UPDATE [order]
SET ProdID = @CustId
OUTPUT #INSERTED.OrderID, #INSERTED.ProdID
INTO @OrderIDs
WHERE CustID = @CustId;
