Chapter 15 Flashcards
Which of the following statements calls the stored procedure and passes the values ‘2019-10-01’ and 122 to its input parameters?
CREATE PROC spInvoiceTotal1
@DateVar smalldatetime,
@VendorID int
AS
SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;
EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = ‘2019-10-01’;
Stored procedures execute faster than an equivalent SQL script because stored procedures are what?
precompiled
Data validation is the process of
preventing errors due to invalid data
Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?
CREATE
You typically use the return value of a stored procedure to
indicate to the calling program whether the stored procedure completed successfully
Which of the following statements returns the value of a variable named @InvoiceCount?
RETURN @InvoiceCount;
Which statement can you use to manually raise an error within a stored procedure?
THROW
If you delete a stored procedure, function, or trigger and then create it again
you delete the security permissions assigned to the object
Which of the following statements calls the following stored procedure, passes the value ‘2019-10-01’ to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal?
CREATE PROC spInvoiceTotal2
@DateVar smalldatetime,
@InvoiceTotal money OUTPUT
AS
SELECT @InvoiceTotal = SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;
(Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)
EXEC spInvoiceTotal2 ‘2019-10-01’, @MyInvoiceTotal OUTPUT;
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by
omitting the parameter name and value from the list
If you want to prevent users from examining the SQL code that defines a procedure, function, or trigger, you code the CREATE statement with the ________________ option
ENCRYPTION
To make a parameter for a stored procedure optional, what do you assign to it?
a default value
Which of the following statements 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.)
EXEC @InvoiceCount = spInvoiceCount;
System stored procedures
perform standard tasks on the current database
are stored in the Master database
can change with each version of SQL Server
Which keyword can you use to pass a parameter from a stored procedure back to the calling program?
OUTPUT
