SQL Stored Procedure Debugging

Today, a colleague asked how to debug a stored procedure in SQL Server (our company is using SQL Server R2), and a look at it, and the previous use of SQL Server 2000 is really very different, I am really dizzy.

So I figured it out a bit. SQL Server 2005 does not know why to remove the important debugger function, to debug, you must install VS2005 Professional version or later. Very inconvenient.

Fortunately, this very important and convenient feature in SQL Server 2008 is back.

However, the debug functionality of SQL Server 2008 differs greatly from the SQL2000 approach. SQL2000 is the stored procedure you want to debug in the Object Browser in Query Analyzer, right-----debugging---Input parameters to start debugging.

The sqlserver2008 is completely different and becomes a must to exec [PROCEDURE NAME] @VAR1 in SSMs, @VAR2, then click on the green triangle or debug---in the Point menu to start debugging. Then click on the rightmost step of the toolbar to debug or jump out. The following variable window and Stack window can be used to view dynamic values such as variables in debugging.

sqlserver2008 debugging requirements and Conditions: If you are debugging on the computer or server where the engine resides, only the SA or Windows user is required to log in. If you are debugging offsite, you will need to set up firewall exceptions, add SSMs and SQLSERVER.EXE to allow, add 135 ports allowed to pass.

In short, SQL2008 debugging than 2000 operation up more trouble, more requirements. At first, I feel as good as 2000, or maybe use 2000. Habits are scary, but Microsoft is constantly improving ...

First, review the earlier versions of SQL Server:

Back in the SQL Server 2000 era, the functionality of Query Analyzer was still rudimentary, far less powerful than vs. In the era of SQL Server 2005, features such as code highlighting, SQL optimization, and so on, are stepping up, but you still can't debug SQL statements. A better third-party SQL syntax Editor does not seem perfect enough to cause some people to complain that stored procedures are inconvenient for maintenance, and that they are not used when developing.

Second, SQL Server Express Smart tips to enhance:

This feature is SQL2008 in SQL Server 2005 after the upgrade, we can easily invoke smart hints, and vs consistent: Use the shortcut key CTRL + J.

As follows:

Is this function the same as vs? After all, they are Microsoft's products.

Third, debug T-SQL statement:

1.Debug ordinary T-SQL statements:

The SQL code is as follows:

1. Use Northwind
2. Go
3. DECLARE @i int, @j int,@k int
4. set @i = 1;
5. set @j = 2;
6. set @[email protected] + @j
7. Select @i;
8. Go

It is very simple to define three variables of type int: I, J, K and simple logical operation of these variables, in management Studio as long as easy to press the F11 key, you can debug the above code block.

As follows:

Then click F11 Debug or F10 Step-by-step debugging code.

As follows:

Do you think this dubug scene is similar to vs?

Iv. support for complex stored procedure nesting debug:

You may wonder if, in a large system, code such as stored procedure nesting stored procedures or nested storage functions can occur if the majority of the database logic is a stored procedure implementation.

Does SQL2008 support debug functionality? The answer is yes.

First define a simple stored procedure (using the Northwind database in this article) with the following code:

2. CREATE procedure Sp_getorders
4. @orderID int = NULL
6. as
8. if (@orderID is null)
10. begin
12. Print ' null '
14. End
16. Else
18. Begin
20. Print ' correct '
22. End
24. SELECT * from Orders whereorderid = @orderID
26. Go

The stored procedure is called within the following batch, with the following code:

1. DECLARE @i int, @j int,@k int
2. Set @i = 1;
3. Set @j = 2;
4. Select @[email Protected] + @j
5. EXEC sp_getorders 10248
6. Select @i;
7. Go

F11 SQL Debug for the above code.

As follows:

When the breakpoint passes through the exec sp_getorders 10248 code, click F11 to enter the sp_getorders stored procedure to make a statement debug.

As follows:

This lets you debug within a nested stored procedure or function, and now you have to admit that the upgraded SQL2008 is becoming more powerful. Are you also afraid of using or debugging stored procedures?

Original from http://blog.csdn.net/dinglang_2009/article/details/6887413

SQL Stored Procedure Debugging