Code a statement that calls the following stored procedure and passes the values

Question

Code a statement that calls the following stored procedure and passes the values ‘2011-12-01’ and 122 to its input parameters. Pass the parameters by name in the reverse order that they’re declared in the procedure.
CREATE PROC spInvoiceTotal1
       @DateVar smalldatetime,
       @VendorID int
AS
SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;
______________________________________________________________________________

Question 9

Code a statement that calls the following stored procedure, passes the value ‘2011-12-01’ to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal. Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.
CREATE PROC spInvoiceTotal2
       @DateVar smalldatetime,
       @InvoiceTotal money OUTPUT
AS
SELECT @InvoiceTotal = SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;
______________________________________________________________________________

10 points   

Code a statement that returns the value of a variable named @InvoiceCount from a stored procedure named spInvoiceCount.
_____________________________________________________________________

Question 11

Code a statement that executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount. Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn’t accept any parameters.
_____________________________________________________________________

Question 12

Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement to create a user-defined table type.

Explanation / Answer

Question 8.

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'
GO

Need to specify for which variable we are passing what values explicitly, if needs to pass values in any order

Question 9.

EXEC spInvoiceTotal2 '2011-12-01', @MyInvoiceTotal output
GO

This will store the out value of @InvoiceTotal variable in spInvoiceTotal2 Stored Procedure into @MyInvoiceTotal variable.

Question 10.

CREATE PROC spInvoiceCount
@InvoiceCount output
AS
SELECT @InvoiceCount = COUNT(*)
FROM Invoices;

This Stored Procedure will return the value of a variable named @InvoiceCount

Question 11.

EXEC spInvoiceCount @InvoiceCount output
GO

This will capture the value returned when spInvoiceCount executed into @InvoiceCount variable.

Question 12.

In order to create a user-defined table type, one should use the CREATE TYPE statement.

Now @MyTest can be used to pass to a Stored Procedure.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.