How to easily debug T-SQL statements and stored procedures under SQL Server 2008 _mssql2008

Today, a colleague asked me how to debug a stored procedure in SQL Server (our company is using SQL Server 2008 R2), and it's really different from the previous use of SQL Server 2000.

So I pondered. In SQL Server 2005, there is no reason to remove the important debugger feature, and to debug, you must install VS2005 Professional Edition or later. Very inconvenient.

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

However, the debugging capabilities of SQL Server 2008 and the SQL2000 approach vary widely. SQL2000 is a stored procedure that needs to be debugged in the Object Browser in Query Analyzer, right----debug---Input parameters to start debugging.

The sqlserver2008 is completely different, and it becomes necessary to ssms in the exec [PROCEDURE NAME] @VAR1, @VAR2, and then the debug---Start debugging in the green triangle or point menu. Then click on the rightmost step of the toolbar to debug or jump out and so on. The following variable window and Stack window can be used to view dynamic changes such as variables in debugging.

Requirements and conditions for sqlserver2008 debugging: If you are debugging on the computer or server where the engine is located, you only need an SA or a Windows user to log on. If you are debugging offsite, you need to set the firewall exception, add SSMs and SQLSERVER.EXE to allow, add 135 ports allow to pass.

In short, SQL2008 debugging than 2000 operation up more trouble, the request is also more. Just start feeling less than 2000 easy to use, it may be using 2000 habits. Habits are scary, but Microsoft is constantly improving ...

First, review earlier versions of SQL Server:

In the SQL Server 2000 era, Query Analyzer was not as powerful as vs. In the SQL Server 2005 era, features such as code highlighting, SQL optimization, and so on were gradually enhanced, but the SQL statements still failed to debug. A little better. Third-party SQL syntax editors do not seem to be perfect, leading some to complain that stored procedures are inconvenient to maintain and that they are not used when they are developed.

Second, SQL Server 2008 Express Smart Tips Enhanced:

This feature is an upgraded version of SQL2008 after SQL Server 2005, and we can easily invoke smart hints, and vs.: Use shortcut keys CTRL + J.

Screenshot below:

Does this feature be the same as vs? After all, they are Microsoft's products.

Third, debug T-SQL statements:

1.Debug Common T-SQL statements:

The SQL code is as follows:
Use Northwind Go
DECLARE @i int , @j int, @k int set @i = 1;
Set @j = 2; Set @k=@i + @j
Select @i; Go

It is very simple to define three int variables: 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.

Screenshot below:

Then click F11 Debug or F10 debug the Code step-by-step.

Screenshot below:

Do you feel that this dubug scene is the same as vs.

Iv. support for complex stored procedures nesting debug:

You may doubt that in a large system, code such as stored procedure nested stored procedures or nested storage functions occurs when most of the database logic is implemented by stored procedures.

Does SQL2008 support debugging? The answer is yes.

First define a simple stored procedure (use the Northwind database in this article) code as follows:
CREATEProcedureSp_getorders@orderIDInt=null    
as     if  (@ Orderid is  null  )    
begin    print  ' null '    
end    else    
begin   print  ' correct '  
end    select  *  from  orders where orderid = @ ORDERID &NBSP
go 

The stored procedure is invoked in the following batch, with the following code:
DECLARE @i int , @j int, @k int set @i = 1;
Set @j = 2; Select @k=@i + @j
EXEC sp_getorders 10248 select @i;
Go

F11 the above code with SQL Debug.

Screenshot below:

When the breakpoint passes the exec sp_getorders 10248 code, click F11 to enter the sp_getorders stored procedure to debug on a per-statement basis.

Screenshot below:

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