PrepAway - Latest Free Exam Questions & Answers

Which Transact-SQL statement or statements should you use?

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure thatmeets the following requirements:
Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the
solution. Choose all that apply.)

PrepAway - Latest Free Exam Questions & Answers

A.
CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT
AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID
RETURN
GO

B.
EXECUTE dbo.GetCustomerRatIng 1745

C.
DECLARE @customerRatingBycustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745
, @CustomerRatingSyCustomer

D.
CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomeriD
RETURN @Result
GO

E.
DECLARE @CustomerRatIngByCustcmer INT
EXECUTE dbo.GetCustomerRating @CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

F.
CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
FROM Customers
WHERE Customer= = @CustomerID
RETURNS @Result

Explanation:
Correct answer AE (see comments and try by yourself) Ax

5 Comments on “Which Transact-SQL statement or statements should you use?”

  1. Matador says:

    A – seems to have incorrect spelling, the proper CREATE PROCEDURE should looks like that (Note @CustomerID). With this change A is correct. The only second part from B,C,E that works is E.
    D requires @Result declaration.
    F syntax is ok. But C & E gives: “Procedure or function GetCustomerRating has too many arguments specified.” with it. B from the other hand, doesn’t return a thing.

    — // Test SQL
    CREATE TABLE Customers
    (
    CustomerOrders INT,
    CustomerValue INT,
    CustomerID INT
    );

    INSERT INTO Customers VALUES(1,1,1)
    INSERT INTO Customers VALUES(2,1,2)
    INSERT INTO Customers VALUES(10,2,1745)

    CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT,@CustomerRating INT OUTPUT
    AS
    SET NOCOUNT ON
    SELECT @CustomerRating = CustomerOrders/CustomerValue
    FROM Customers WHERE CustomerID = @CustomerID
    RETURN
    GO

    DECLARE @CustomerRatingByCustomer INT
    EXECUTE dbo.GetCustomerRating @CustomerID = 1745,
    @customerRating = @CustomerRatingByCustomer OUTPUT

    SELECT @CustomerRatingByCustomer;




    0



    0

Leave a Reply