Apply Your Knowledge

Exercises

9.1 Creating a Stored Procedure

This exercise demonstrates how to create a new stored procedure to select the names of all of the indexes for a given table in alphabetical order.

Estimated Time: 10 minutes

1. Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Databases container.

2. Open the Master database and click on the Stored Procedures container.

3. Right-click under the stored procedures listed on the right and choose New Stored Procedure.

4. Enter the following text:

    create procedure sp_GetIndexList @TableName sysname as select name from sysindexes where id = object_id(@TableName) order by name 

5. Click the Check Syntax button. If you don't receive the Syntax Check Successful! dialog box, you need to check your typing.

6. Click OK to clear the dialog box, and OK again to save your stored procedure.

9.2 Executing a Stored Procedure

This exercise demonstrates how to run a stored procedure. It assumes you've already completed Exercise 9.1. If you skipped it, shame on yougo back.

Estimated Time: 5 minutes

1. Open SQL Server Query Analyzer and log into the server used in Exercise 9.1.

2. Enter use pubs to use the Pubs database.

3. Enter exec sp_GetIndexList 'authors' .

4. Run the query.

9.3 Creating a User -Defined Function

This exercise demonstrates how to create a user-defined function by creating a function that returns the mathematical constant e , the base for natural logarithms.

Estimated Time: 5 minutes

1. Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Databases container.

2. Open the Pubs database and click on the Use Defined Functions container.

3. Right-click under any user-defined functions in the right pane and choose New User Defined Function.

4. Enter this text into the window:

    create function e () returns float begin return exp(1) end 

5. Click the Check Syntax button. If you don't receive the Syntax Check Successful! dialog box, you need to check your typing.

6. Click OK to clear the dialog box, and OK again to save your user-defined function.

9.4 Calling a User-Defined Function

This exercise shows how to call a user-defined function. It relies on Exercise 9.3 being completed, so if you didn't do that yet, go stand in a corner for a bit, and then do it.

Estimated Time: 5 minutes

1. Open SQL Server Query Analyzer and log into the server used in Exercise 9.3.

2. Enter use pubs to use the Pubs database.

3. Enter select dbo.e() .

4. Run the query.

Review Questions

1:	What are the differences between a stored procedure and a user-defined function?
A1:	The principal differences are side effects and output. A stored procedure is allowed to change data values in tables, change table schemas, and generally write any data anywhere within the permissions of the creator of the stored procedure. A user-defined function cannot directly change data. Also, a stored procedure has many different options for output: return value, resultset, output parameters, and any combination thereof. A function can return only one of two things: a scalar value or a table.
2:	Why are stored procedures faster than just re-running the same batch over and over again?
A2:	Stored procedures can be faster because they have pre-cached execution plans, so SQL Server doesn't have to recompile the T-SQL every time it runs, which it does have to do for batches and scripts.
3:	What is the difference between a multi-statement table-valued function and an inline table-valued function?
A3:	Inline table-valued functions consist of one select statement. Multi-statement table-valued functions can contain additional logic.
4:	Why would you use an inline function rather than a view?
A4:	You would use an inline function rather than a view to provide increased flexibility. A view cannot take a parameter, so its output is fairly fixed, whereas an inline function can filter output based on input parameters.
5:	Why is an EXEC not required on the first line of a batch?
A5:	EXEC is not required on the first line of a batch, so lazy system administrators can type sp_who as the only thing in a batch. After all, EXEC sp_who is a lot more typing.

Exam Questions

1:	Which of the following problems would be appropriately implemented with a scalar user-defined function? Returning a list of customer names. Updating a customer name to upper case. Getting the absolute value of a number. Calculating a customer's outstanding account balance.
A1:	D. Option A needs to return a list, not a single value; Option B would change data, which is not allowed; Option C is already implemented through a system-defined function. Option D needs to return only a single number, the value of the bill, so it's the best answer. You can find more information in the section titled, "Types of User Defined Functions".
2:	Which of the following is not a valid reason to use a view rather than an inline user-defined function? Need to update the recordset directly. Need to return a recordset with a consistent set of parameters. Need to combine data from several tables into one resultset. Need to be able to change the filter on the recordset.
A2:	D. Options A, B, and C are all possible using a view. Option A is not possible with a user-defined function. Option D is not possible with a view; a view has a static filter on the output. The section titled "When to Use Functions or Stored Procedures" covers this in some detail.
3:	Eric is having a problem getting his stored procedure to run with acceptable performance with all the different parameters he can use. Basically, the parameters specify whether a search should be done on last name, first name, or address, or some other parameter in his customer table. What can Eric do that may improve performance? Use views rather than stored procedures. Create the procedure using the WITH RECOMPILE option to avoid using the cached query plan. Create the procedure in Enterprise Manager rather than Query Analyzer. Use more user-defined functions to handle the parameter input.
A3:	B. Option A doesn't allow enough flexibility in the resultset; Options C and D would make absolutely no difference at all. The "Implementing Error Handling in Stored Procedures" section talks about how to effectively handle these situations.
4:	Kyle is writing a system that will monitor the number of customer accounts in the database and, when an account representative has exceeded the number of accounts he is allowed to manage, send an email to the sales manager. He's going to use the SQL Server Agent to schedule the job to run nightly. Which of the following tools can he use to send the email? A stored procedure. A table-valued user-defined function. A scalar user-defined function. An inline user-defined function.
A4:	A. User-defined functions cannot send email; sending email is a side effect, and user-defined functions are not allowed to have side effects. This is also mentioned in the "When to use Functions or Stored Procedures" section.
5:	Stan is trying to figure out why his user-defined function isn't working properly. He keeps getting an "incorrect syntax near the keyword 'return'" error. What's wrong? create FUNCTION distance ( @x1 int = 0, @y1 int = 0, @x2 int = 0, @y2 int = 0 ) return float as begin declare @distance float set @distance = sqrt ( power(@y1 - @y2, 2) + power(@x1 - @x2, 2) ) return @distance end Side effects are caused by returning a value of type float . A comma is missing after the @y2 parameter. The return float should be returns float . A parenthesis is missing on the SET @distance statement.
A5:	C. The return and returns do different things, but that's a very common typographical error to make. It's all covered in the "Creating User-Defined Functions" section.
6:	Kenny is trying to pass values into a stored procedure. Here's what the CREATE PROCEDURE statement looks like: create Procedure CalculateSalesTax @ItemPrice float, @Qty int, @TaxRate float, @SalesTax float OUTPUT Which of the following EXEC statements return the sales tax into the @SalesTax variable? EXEC CalculateSalesTax 2.39, 1, 4, @SalesTax EXEC @SalesTax = CalculateSalesTax 2.39, 1, 4, 0 EXEC CalculateSalesTax @ItemPrice = 2.39, 1, 4, @SalesTax OUTPUT EXEC CalculateSalesTax @ItemPrice = 2.39, @Qty = 1, @TaxRate = .04, @SalesTax = @SalesTax OUTPUT
A6:	D. Option A will run without an error, and won't return the result. Option B will run without an error, and capture the return code into the @SalesTax variable, which can't handle the floating point number that is the @SalesTax . Option C won't work because the first parameter is specified by name, but the rest are specified positionally, which is a syntax error. This is covered in the "Calling Stored Procedures, Passing and Returning Parameters to and from Stored Procedures" section.
7:	Wendy has created a stored procedure to insert new values into her database: create procedure InsertPlayer @PlayerID int, @Comment varchar(2000) as begin insert into CommentTracker values (@PlayerID, @Comment) exec xp_sendmail @message = @Comment, @recipients = 'WendyT', @subject = 'Comment Activity' end The problem is that sometimes the insert fails with a constraint violation, and she'd like an email when those fail also. Which of the following approaches would enable this to happen? Capture the value of @@ERROR and use an IF statement to send an email if the INSERT statement fails. Capture the value of @@ERROR and use a WHILE loop to retry the insert in the event of an error. Combine the INSERT and the xp_sendmail into a transaction. Capture the value of @@LAST_ERROR and use an IF statement to send an email if the INSERT statement fails.
A7:	A. Using an IF statement with @@ERROR sends the email. Option B won't send an email, and will probably result in a particularly nasty infinite loop; Option C won't work because it doesn't send the email on failure, and Option D won't work because @@LAST_ERROR isn't a valid system function. The section on "Implementing Error Handling in Stored Procedures" talks about the correct ways to handle errors in stored procedures.
8:	What's wrong with this user-defined function? create function InsertPlayer (@PlayerID int, @Comment varchar(2000)) returns int begin insert into CommentTracker values (@PlayerID, @Comment) return 1 end It's poorly formatted. It's missing the keyword AS before the BEGIN . It has a side effect. It is table-valued.
A8:	C. INSERT statements into local tables are not allowed within functions because they have the side effect of changing data in a table somewhere. Formatting is not relevant to how well the function operates; the keyword AS is optional in a CREATE FUNCTION statement (but required in a CREATE PROCEDURE ); and the function is scalar, not table valued, but that doesn't even matter. The "Creating User-Defined Functions" section examines this in some detail.
9:	BeBe is trying to write a stored procedure that will return a subset of a table back to her script. Which of the following methods will not work? Use a temporary table defined inside the stored procedure. Use a static cursor as an output parameter. Insert data into a temporary table that's defined before calling the stored procedure. Use a variable of type table as an output parameter.
A9:	A. If a temporary table is created inside a stored procedure, it is dropped when the stored procedure terminates, so this won't work to get tabular data outside the stored procedure. To find out how to pass data into and out of stored procedures, check out the section called "Calling Stored Procedures; Passing and Returning Parameters to and from Stored Procedures".
10:	Given the following stored procedure declaration: CREATE PROC Test2 @I int, @J float, @K varchar(2000) Which of the following EXEC statements will work? EXEC Test2 4.3, 19, 27 EXEC Test2 @J=4.3, @I = 20, @K = 'value' EXEC Test2 20, @J=4.3, 'value' EXEC Test2 20, @J=4.3, @K = 'value' OUTPUT
A10:	B. Option A is a type mismatch because trying to put the value 4.3 into an integer doesn't work. Option C has a positional parameter after a named parameter, which won't work, and Option D has something as OUTPUT which isn't declared that way. The section on "Calling Stored Procedures; Passing and Returning Parameters to and from Stored Procedures" has a lot of information about how to declare parameters.
11:	To properly process an insurance claim, Esther has to have a client ID number and a claim amount. On claims over a certain amount of money, she has to have the name of the adjuster. Which of the following would be the best way to make sure that Esther had entered all the information properly before trying to process the claim? A multi-line table-valued stored procedure. An inline function A cursor A stored procedure
A11:	D. Option A is nonsense . Option B won't work, because functions can't actually record anything. Option C won't work because cursors aren't used to validate data. The "When to Use Functions or Stored Procedures" section talks about how this works.
12:	Which of the following accepts the return value from the stored procedure CheckInsuranceClaim ? declare @RetVal float = CheckInsuranceClaim @ClaimID = 41400 declare @RetVal int exec CheckInsuranceClaim @ClaimID = 41400, @RetVal OUTPUT declare @RetVal int exec CheckInsuranceClaim @ClaimID = 41400 declare @RetVal int exec @RetVal = CheckInsuranceClaim @ClaimID = 41400
A12:	D. Option A is an incorrect declare statement; Option B would be valid for an output parameter, but not a return value; and Option C just ignores the whole return value issue altogether. The "Calling Stored Procedures; Passing and Returning Parameters to and from Stored Procedures" section covers how to declare stored procedures properly.
13:	Which of the following inline function definitions would correctly return the information for the customer with CustomerID 42? Create Function GetCustomerInfo @CustomerID int = 42 returns select * from customer where customerid = @CustomerID Create Function GetCustomerInfo ( @CustomerID int = 42 ) returns table as return (select * from customer where customerid = 42) Create Inline Function GetCustomerInfo ( @CustomerID int = 42 ) returns (select * from customer where customerid = 42) Create Function GetCustomerInfo ( @CustomerID int = 42) returns @ReturnTable table (CustomerID int, CustomerName varchar(50)) as begin insert into @ReturnTable select * from customer where customerid = @CustomerID return end
A13:	B. Option A is missing a lot of parentheses; Option C is wrong because there isn't a CREATE INLINE FUNCTION statement; and Option D is a multi-line table-valued function, not an inline. Using user-defined functions in this manner is covered in the "Filtering Data with User-Defined Functions" section.
14:	Which of the following is true? A stored procedure must have a return statement. A function must have a return statement. A stored procedure must have output parameters. A stored procedure cannot return a table.
A14:	B. Functions must have return statements. Stored procedures are not required to, but are allowed to, and can return only integers. Stored procedures can return variables of type table , and they don't have to have an OUTPUT parameter. The sections titled "Creating User-Defined Functions" and "Creating and Managing Stored Procedures" cover how the two different types of objects are created.
15:	After examining his stored procedure that runs one statement, an INSERT , Larry determines that he's going to have a problem with users passing in bad data, which will cause inserts to fail because of constraints. Which of the following is the best approach to handling the situation? Use an inline function to check the constraint first. Check all the applicable constraints with SELECT statements before performing the insert. Attempt the insert and check the @@ERROR variable after the insert. Remove the constraints.
A15:	C. Options A and B are going to have the same problem: If the constraints on the tables change, the user-defined function or the stored procedure will need to be rewritten. Option D would be nice, but the constraints are probably there for a reason, and removing them just so an INSERT can put suspect data into a table is not the correct approach. If you're in doubt about whether it's appropriate to use a stored procedure or a user-defined function, read the section on "When to Use Functions or Stored Procedures".

Suggested Readings and Resources

SQL Server Books Online

• CREATE PROCEDURE

• CREATE FUNCTION

• SQL Server Architecture: SQL User-Defined Functions