Debugging a Stored Procedure

Debug a stored procedure using temporary tables and breakpoints

Prerequisites

You must be connected to the database that contains the stored procedure you want to debug.

Create the stored procedure that references a temporary table:
1. Right-click the Stored Procedures folder and select New > Stored Procedure.
2. Follow the wizard instructions to create the stored procedure. When you click Finish, the Stored Procedure editor opens.
3. In the Source tab, add a SQL statement that references a temporary table. That is, replace the line: Print 'Replace this Print line with your SQL statements'with your SQL statements. See the example below.
Save the stored procedure. The first time you save, run, or debug a stored procedure that references a non-existent temporary table, the New Temporary Table wizard guides you through creating the table, prompting you to define required fields and datatypes for the table. Once created, the table exists throughout the session. The definition, create, and other insert statements used to create the table are preserved as annotations in the source code of the stored procedure if you select the Insert the SQL Statement as Annotations checkbox.
Note: By preserving the annotations, you need only define the temporary table once. WorkSpace automatically creates the table in subsequent invocations of the stored procedure based on the annotations (even after the table is dropped).
Set breakpoints in your stored procedure. The breakpoints are used to debug the stored procedure by stepping through it and observing the results while in the database debugger.
Open the stored procedure in the Database Debug perspective. Right-click in the body of the stored procedure and select Debug. When prompted to switch to the Database Debug perspective, answer Yes.
Debug the stored procedure. From the Database Debug perspective, you can step through the stored procedure, view results, edit temporary table values, and so on.

Stored procedure referencing a temporary table

In this example, you create two stored procedures (Bproc and Aproc) that references a temporary table. Once created, you could set breakpoints to debug the stored procedures.

Create a stored procedure named Bproc:

CREATE PROCEDURE dbo.Bproc
AS
BEGIN
   Print 'This is Bproc'
END

In the Source tab of the Stored Procedure editor, add a 'select' statement to Bproc:
```
Select * from #qty_table
```

Save the stored procedure – because the stored procedure references a temporary table (#qty_table), the New Temp Table wizard displays when you save it.

Enter the correct column names, column types, and optionally add the 'create index' and 'insert' statements.

The annotations are generated when successfully saved :

 /**
Warning: This annotation was generated by
temporary table wizard. Do not edit it. 
   @TemporaryTableName #qty_table

   create table #qty_table (
   lowqty int,
   highqty int
   )

--The following statement(s) will be executed
automatically after creation of this temp table.
--User is responsible for the statement(s)'s
content and validation.

   --Add index creation statement below
   CREATE NONCLUSTERED INDEX idx_dbidname
   ON #qty_table (lowqty, highqty)

   --Add insert statement below
   insert #qty_table values (9, 999)
   insert #qty_table values (8, 899)
   insert #qty_table values (7, 799)
   insert #qty_table values (6, 699)
*/

CREATE PROCEDURE dbo.Bproc
AS
BEGIN
   Print 'This is Bproc'
    select #qty_table.lowqty, #qty_table.highqty from 
#qty_table
END

Create the stored procedure, Aproc:

CREATE PROCEDURE dbo.Aproc
AS
BEGIN
   Print 'This is Aproc'
END

Copy the create table, create index, and insert data statements from the annotations (in Bproc) to the body of the stored procedure Aproc. Also, add the statement 'exec Bproc' to execute the stored procedure Bproc from Aproc:

CREATE PROCEDURE dbo.Aproc
AS
BEGIN
   Print 'This is Aproc'
   create table #qty_table (
   lowqty int,
   highqty int 

 )
   ALTER TABLE #qty_table ADD PRIMARY KEY CLUSTERED 
   (lowqty ASC )
   CREATE NONCLUSTERED INDEX idx_dbidname ON #qty_table
   (lowqty, highqty)

   select #qty_table.lowqty, #qty_table.highqty from 
   #qty_table
   insert #qty_table values (9, 999)
   insert #qty_table values (8, 899)
   insert #qty_table values (7, 799)
   insert #qty_table values (6, 699)
   exec Bproc
END