Debug a T-SQL Stored Procedure

1) In a new SQL Server project, establish a connection to the AdventureWorks sample database.

2) Create a new stored procedure using the code from the first example section below, and name it HelloWorld.


CREATE PROCEDURE HelloWorld
AS
DECLARE @mynvarchar NVARCHAR(50),
@myfloat FLOAT
SET @mynvarchar = @@VERSION
SET @mynvarchar = 'Hello, world!'
SET @myfloat = 1.6180
PRINT @mynvarchar
RETURN (0)

3) Set breakpoints in HelloWorld, and step into the stored procedure. The instruction pointer, designated by a yellow arrow, will appear on the line SET @mynvarchar = @@VERSION, the first executable line of code in the stored procedure.

4) Try out different debugging features.

4.1) Make the Locals window visible. To do so, on the Debug menu, click Windows, and then click Locals. Notice that the parameters and local variables are displayed in the Locals window with their corresponding values. You can edit the values of the variables in the Locals window as the stored procedure runs.

4.2) Press F10 to step one line in the stored procedure. Notice that the value of the variable @mynvarchar has changed in the Locals window and its value is now displayed in red, indicating it has changed.

4.3) Make the Watch window visible. To do so, on the Debug menu, click Windows, and then choose Watch.

4.4) In the Text Editor, double-click the @mynvarchar variable to select it. Drag @mynvarchar to any location on the Watch window. The variable is now added to the list of watched variables.

4.5) In the Text Editor, right-click the line Return (0), and on the shortcut menu, click Insert Breakpoint.

4.6) On the Debug menu, click Continue.

5) Choose Continue again to finish debugging the stored procedure.