Week 3

Day 17
Procedures and Performance

Yesterday you started writing stored procedures. You learned all the critical syntax, and you wrote several working examples.

Today we are going to look at procedure performance issues.

---

Warning:  This chapter and the next one are not for the faint of heart. They contain a great deal of detailed information about how procedures work, some close analysis of errors and transactional problems, along with approaches to avoiding data integrity crises in your system. Good luck!

---

Today’s topics include:

•  Stored procedure debugging methods

•  Procedures and performance

•  On-the-fly execution

Stored Procedure Debugging Methods

Let’s start by looking at the methods available to handle stored procedure debugging. It’s actually pretty hard to debug procedures because the tools are limiting, and your control of what happens on the server is quite limited. There is no stepping debugger and no simple way to make one up, so you can’t directly observe the values of parameters and variables without printing them. You can’t establish a break point and examine the contents of a worktable halfway through a procedure, and if you try to look at the contents of variables and temporary tables after a procedure exits, both are discarded and made unavailable.

Create a Good Work Environment

Let’s look at some things that you can do. First, learn to use both styles of T-SQL comments. Multiline comments will help you comment out large chunks of code, and they can be nested. In this example, I’ve commented out the procedure creation so that I can execute the procedure as a batch:

/*
create   proc      pr_insertorder
(@item_id   int,
 @qty    nt,
  @order_id   int   output)
a s
* /
-- Declarations for testing
declare @item_id int, @qty int, @order_id int
declare @priceval money, @retstatus int
…

---

Tip:  When you run a procedure as a batch, you will have a problem with your RETURN statements (only a procedure can include a return status value). Go through the procedure and comment out the return status values like this:

Before:

return 99

After:

return -- 99

---

Use single-line comments to add or remove debugging commands, including DECLARE and PRINT statements:

create   proc      pr_insertorder
(@item_id    int,
 @qty   int,
 @order_id    int   output)
a s
-- Declarations   for   testing
-- declare   @item_id   int, @qty int,   @order_id int
-- print   “Starting   execution   now”
declare   @priceval   money,   @retstatus int
…

In this example, a single-line comment has been used to remove the DECLARE statement that declared the variables that were used during the debugging process. Another single-line comment has eliminated the debugging print message. Leave these debugging messages in the procedure but commented out. They can help you eliminate problems in procedures when you are modifying them later.

Take Small Bites

This piece of advice applies to both the composition and debugging of procedures. From the standpoint of debugging, one error in a procedure can result in several error messages during the compile or at runtime. Always treat the first problem mentioned, and then move on. Here’s an example of a problem procedure:

create procedure abc
(@parm_d    datetime,
  @parm_c  int =   ‘75’ output)
a s
declare     @var_dr      date
select     @var_d = getdate()
if @parm_d > @var_d
begin
     print “Later than today”
   return 99
end
select *
      from titles
  where pub_date > @var_d
     and ytd_sales > @parm_c
return  0

Msg  137,  Level  15,  State  1
Must   declare   variable    ‘@var_d’.
Msg  137,  Level  15,  State 2
Must   declare   variable    ‘@var_d’.
Msg  137,  Level  15,  State 2
Must   declare   variable    ‘@var_d’.

This is a pretty terrible procedure all around, but we’re mostly concerned with debugging it, not with giving it greater relevance. When the CREATE statement is executed, SQL Server gives a raft of complaints about the nonexistence of a variable. Fix that problem; then re-execute the CREATE statement to find more problems:

declare @var_d  datetime

This time we got the name of the variable right and fixed the data type as well.

---

Tip:  When you are composing stored procedures, it makes sense to write the procedure slowly, testing individual components, and then finally knit the whole program together when all of its parts are trouble-free. One easy way to do that requires that you learn to execute part of a script in ISQL/w.

From a query window, highlight a single query in a script you want to execute. When you press Ctrl+E or the green arrow button, ISQL/w executes only the part of the script that is highlighted.

---