Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Using Output Parameters with a Stored Procedure

One of the ways that you can use a stored procedure is to have it return data through output parameters. Then after the call to the stored procedure you can display the values returned to you. For example, you might use a stored procedure to return summary information about a table. This could be done through output parameters. The technique presented in this section of the chapter shows you how to use output parameters.


USE IT A Command object contains a collection of Parameter objects. These parameters are values that the stored procedure passes back to you after the call is complete. This ASP.NET page calls a stored procedure that returns values. The values returned are the total number of records in the Employees table and the name of the employee who is paid the most.

The stored procedure has this definition:

CREATE PROCEDURE Tablelnfo
@RecordCount integer OUTPUT,
@TopSalaryName varchar(100) OUTPUT
AS
Select @RecordCount = Count(EmpID) From Employees Select @TopSalaryName = LastName + ' + FirstName
From Employees Where Salary = (Select Max(Salary) From Employees)
Totice that the two parameters are declared as output parameters. In the first query, one of the put parameters is set to the record count. In the second query the other parameter is set to the name he employee who is paid the most.

That stored procedure is called when the ASP.NET page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
[n that procedure you will need a Connection object:
Dim DBConn as SQLConnection that points to the SQL Server database:
DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;"
& "User Id=sa;"
& "Password=yourpassword;")
You will need a Command object: Dim DBSP As New SQLCommand

You also will need two Parameter objects. The first has its name match the first parameter in the stored procedure and is of the same data type:

Dim parRecordCount as New _ SqlParameter("@RecordCount", SqlDbT e.Int)

The second parameter is set to the name and type of the second output parameter in the stored procedure:

Dim parTopSalaryName as New _ SqlParameter("@TopSalaryName", SqlDbType.VarChar, 100)

Both parameters are set to output parameters using the Direction property:

parRecordCount.Direction = ParameterDirection.Output parTopSalaryName.Direction = ParameterDirection.Output

Next, those parameters are added to the Parameters collection of the Command object:

DBSP.Parameters.Add(parRecordCount) DBSP.Parameters.Add(parTopSalaryName)

The CommandText property of the Command object is set to the name of the stored procedure: DBSP.CommandText = "TableInfo" and the command type is set to stored procedure: DBSP.CommandType = CommandType.StoredProcedure

The Command object will connect to the database through the Connection object:

DBSP.Connection = DBConn DBSP.Connection.Open
The stored procedure can then be executed: DBSP.ExecuteNonQuery()

After the call to the stored procedure you can use the values in the output parameters through the Value property:

lblMessage.Text = "Total Employees: "
& parRecordCount.Value _
& "

Highest Paid: "
& parTopSalaryName.Value
Visitors would then see through the Label control the total number of employees and the highest salary amount.

Sending Data to a Stored Procedure Through Input Parameters

If you are working towards making your ASP.NET application more efficient and that application connects to an SQL Server database, you will want to create stored procedures that do basic things like adding records, deleting records, and other such tasks. With these types of stored procedures, you need to pass data into them. This technique shows you how to call a stored procedure that needs to have parameters passed into it.


USE IT The ASP.NET page created for this technique allows visitors to add a record to the Employees table. Instead of using an Insert statement in the ASP.NET code, a stored procedure is called to add the record.

That stored procedure has this definition:

CREATE PROCE @LastName va
DURE AddEmployee rchar(50),
@FirstName varchar(50), @BirthDate datetime, @Salary money,
@EmailAddress varchar(50) AS
Insert Into Employees (LastName, FirstName, BirthDate,
Salary, EmailAddress) values
@LastName, @FirstName, @BirthDate,
@Salary, @EmailAddress) GO
Notice that the stored procedure has five input parameters. Those parameters are used in the Insert statement in the stored procedure.

On the ASP.NET page, visitors enter the values for the new employee record into TextBox control. When they click the Button control, this procedure fires, adding the new record by calling the stored procedure:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)
Within the procedure, you need these data objects:
Dim DBConn as SQLConnection Dim DBAdd As New SQLCommand
You need to start by connecting to the SQL Server database:
DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")
Next, you place the SQL syntax into the Command object that calls the stored procedure:

DBAdd.CommandText = "Exec AddEmployee " _
& "'" & Replace(txtLastName.Text, "'", """) _
&
& ''" & Replace(txtFirstName.Text, "'", """)
&
& "'" & Replace(txtBirthDate.Text, "'", """)
& HI,
& Replace(txtSalary.Text, "'", """) & ", "
& "'" & Replace(txtEmailAddress.Text, "'", """)
Notice that the call passes in five parameters, the values for the new record. A comma separate each parameter.

The Command object will connect to the database through the Connection object:

kdd.Connection = DBConn kdd.Connection.Open

and the stored procedure can be executed:

kdd.ExecuteNonQuery()

Retrieving Data from a Stored Procedure

Stored procedures frequently return data from a database. They can return single values, tables joined together, or all the records from a single value, and much more. You can call stored procedures that return data from your ASP.NET pages. This technique shows you how to do that..

USE IT The page defined for this technique displays in a DataGrid control all the employees who have a birthday in the current month. The DataGrid has this definition:

asp: datagrid
id="dgEmps"
runat="server" autogeneratecolumns="True"
/