SQL Stored Procedure Debugging from SQL Query Analyzer

Introduction

Debug is the way to show your applications data flow or Execution flow

MS SQL Server 2000 provides to debug the stored procedures, Step by step debugging shows the flows of execution ponters.

Permissions and Security

system procedure sp_sdidebug provide to enable your debugging in yout T-SQL AQuery analyzer,
Before of debugging you need to ececute this system pocedure
sp_sdidebug 'LEGACY_ON' Same thing you can also off the debugging using this
sp_sdidebug 'LEGACY_OFF'Be careful that any other SQL permissions or authentication is still in effect or attempt to debug using a different user account than the actual bug demonstrates.

Debugging

Once you have the object browser tree view visible, select a stored procedure in a database and right-click on the stored procedure name. The right-click menu should have a 'debug' option at the very bottom.

If this Debug in menu option is not enabled then, you should review the permissions associated with your current user

Debug Procedure

Right click on the stored procedure name in the object browser tree view and choose 'debug'. This will bring up a 'Debug Procedure' window for you to enter initial values for any parameters

Same thing pass the value of second parameters then execute.

Summary

SQL Server 2000 Debugging is a huge step forward. You can have complex, nested code in stored procedures and be able to test them confidence regarding the statement of the database at each statement. This allows you to keep the transactional power and processing speed on the SQL server instead of trying to reinvent those features in the calling application.