How to debug a Stored Procedure in visual studio

There are two way to debug a stored procedure in visual studio.

I am going to discuss both one by one.
##############    1st method    ######################

open server explorer in visual studio.

open / add new connection to database.

drill down untill u find your store procedure.

right click on your procedure and then click Step Into Stored Procedure.

The Run stored procedure dialog box opens, which lists the parameters of the stored procedure if required. provide the input parameter if required and click OK
 

a window opens that displays the text of the stored procedure. 

The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion.
 

In the Output window, the following message is displayed, which indicates successful execution: 

 The program ‘SQL Debugger: T-SQL’ has exited with code 0 (0×0).

################    end 1st Method   ########################

################    2nd method    ###########################

create your application and write your desired code.

set a breakpoint in the code where you calls the store procedure.

like at

SqlDataReader dr = cmd.ExecuteReader();

line

In Solution Explorer, right-click the project (not the solution) and open the Property pages. Click Configuration Properties in the tree and then click to select the SQL Server Debugging check box on the Debugging page to enable stored procedure debugging.

In Server Explorer, locate and open the stored procedure . Right-click the stored procedure and then click Edit Stored Procedure.

set a Breakpoint in the store procedure where you want to debug.

run the application

Press F11. Code execution steps from the ExecuteReader method into the stored procedure window.

Press F11 again and procedure executes line by line as you press F11. Then control returns to your Visual Basic project, and the project runs to completion.

To continue to step through the Visual Basic code after you step out of the stored procedure, you must set a second breakpoint in the Visual Basic code after the call to the stored procedure. For example, you can set the second breakpoint on the following line:While (dr.Read)

##############    end 2nd Method   #####################