By now, it should be obvious that stored procedures and triggers can represent a significant portion of your application's code. Even so, for a long time, no decent debugger support existed for stored procedures. (The typical debugging tool was the liberal use of PRINT statements.) Some ISVs jumped in and helped by adding some "pseudo-debugging" products ”but these products didn't have access to the actual SQL Server execution environment. They typically added debugging support by doing tricks behind the scenes, such as adding additional PRINT statements or adding SELECT statements to get the current values of variables . Although some of these products were helpful, they had significant limitations: typically, they couldn't step into nested stored procedures or into triggers. A Transact-SQL debugger was always prominent on the wish list of SQL Server customers. Anybody who has done any serious coding in Transact-SQL will know firsthand how frustrating it is not to have a proper debugger and probably has spent many late nights putting PRINT statements into stored procedures.
Still, it didn't make sense for the SQL Server developers to write a new SQL Server_specific debugger because there were already too many debuggers on the market. If you're like most programmers, you want to do your work in one development environment. For example, if you are a C programmer, you probably want to use the same debugger on your SQL code that you use on your C code. Or if you program in Visual Basic, you probably will want to use the Visual Basic development environment for debugging. Fortunately, this environment-specific debugging capability now exists and its availability is rapidly expanding. If you are a developer in Microsoft Visual C++, Microsoft Visual J++, or Visual Basic, you can now debug Transact-SQL using the same debugger you use in those environments.
To accomplish this, the SQL Server developers defined a DLL and a set of callbacks that SQL Server would load and call at the beginning of each SQL statement. In essence, they defined a set of debug events that would allow them to control the execution on a statement-by-statement basis within SQL Server. This has come to be known as the SQL Server Debug Interface, or SDI. The interface that originally shipped with the version 6.5 release was a work in progress. SDI's first customer was Visual C++ version 4.2. SQL Server 6.5 shipped several months before Visual C++ 4.2, and, of course, the developers didn't get the interface quite right. Specs are never perfect, and they nearly always get tweaked ”at a minimum ”during implementation. This was certainly true for SDI, so to use it with Visual C++ version 4.2 or later you need SQL Server version 6.5 with Service Pack 1 or later. The developers debugged the debugging interface using the Visual C++ team as guinea pigs, and now other development tools are adding support for SDI. Today, SDI is available with Visual C++, Visual Basic, Visual J++, and other development tools from Microsoft. (The exact packaging is always subject to change, but in general, the debugger support for Transact-SQL is available only in each product's Enterprise edition.)
Although the interface is quite likely to change in future releases, it is also made available via a technical note to ISVs that want to add SQL Server debugger support. SDI is a specialized interface of interest only to those writing debuggers, so it is not considered a general feature of SQL Server.
With the existence of SDI, you now have a real debugging environment for Transact-SQL if you're using Microsoft Visual Studio. For example, as a C/C++ developer using Visual Studio, you can:
The SDI is implemented via the pseudo_extended stored procedure sp_sdidebug . (The sp_ convention was used so that the procedure could be called from any database without being fully qualified.) By "pseudo," we mean that, like a normal extended stored procedure, you will see an entry in the sysobjects table of type X for sp_sdidebug . But unlike a normal extended stored procedure, the code for sp_sdidebug is internal to SQL Server and does not reside in a separate DLL. This is true for a few other procedures as well, such as the remote cursor calls made by ODBC and DB-Library cursor functions. This was done so that new capabilities could be added to the server without having to change the tabular data stream (TDS) protocol that describes result sets back to the client application. It also eliminates the need for new keywords ( potentially breaking a few applications) when the commands are of the sort that would not be executed directly by an application anyway.
You should never call sp_sdidebug directly. The procedure exists to load a DLL that the provider of the debugger would write and to toggle debugging on and off for the specific SQL Server connection being debugged. When debugging is enabled, the DLL is given access to internal state information for the SQL Server connection being debugged. All of the APIs defined in the interface are synchronous calls, and they are called in the context of the thread associated with the connection, which allows for callbacks to SQL Server to occur in the context of the client's thread. The internal Process Status Structure (PSS) in SQL Server holds status information for the connection being debugged, and the DLL is then able to read this structure to determine local variable, parameter, system function, and symbol information.
The debugging support of the Enterprise editions of the programming tools seems pretty normal if you are already familiar with the environment. Typically, the biggest problem people have with debugging Transact-SQL is getting it configured in the first place. Here are some tips that might help to you in debugging Transact-SQL from Visual Studio:
For simple debugging, you might find yourself still using the PRINT statement; for tougher problems, you might come to regard the new debugging capability as a lifesaver. Ron Soukup demonstrated this capability at the SQL Server Professional Developer Conference in September 1996, and this five-minute sidebar of his two- hour presentation seemed to generate more interest than anything else he covered!
The ability to formulate and then execute a string dynamically in SQL Server is a subtle but powerful capability. Using this capability, you can avoid additional round-trips to the client application by formulating a new statement to be exe-cuted directly on the server. You can pass a string directly, or you can pass a local variable of type char or varchar. This capability is especially useful if you need to pass an object name to a procedure or if you want to build an SQL statement from the result of another SQL statement. For example, suppose that we had partitioned our database to have multiple tables similar to the authors table. We could write a procedure like the one shown below to pass the name of the table we want to insert into. The procedure would then formulate the INSERT statement by concatenating strings, and then it would execute the string it formulated:
CREATE PROC add_author @au_id char(11), @au_lname varchar(20), @au_fname varchar(20), @tabname varchar(30) AS BEGIN DECLARE @insert_stmt varchar(255) SELECT @insert_stmt="INSERT " + @tabname + " (au_id, au_lname, au_fname, contract) VALUES ('" + @au_id + "','" + @au_lname + "','" + @au_fname + "', 1)" EXECUTE (@insert_stmt) END EXEC add_author '999-99-1234', 'Pike', 'Neil', 'authors'