Steps to Debug stored procedure of SQL server 2008 using Visual Studio 2010
Perform all these steps on machine where SQL server is installed
1. Window key + R and Write: compmgmt.msc
2. Above step will open computer management dialog, in the left pane expand tree view from system tools >> local users and groups >> groups. Double click "Administrators" which appear in the right pane, As shown below
3. On double clicking Administrators following dialog appears, in which you have to add the user name from where you are going to connect to the server using visual studio 2010 and want to debug. So to add user click add button as shown below.
4. When Add... button is clicked following dialog appears, in which you give user name and click ok as shown in below dialog box.
5. Now switch to the visual studio 2010, and open "Server Explorer", and then click "connect to database" button as shown below.
6. Fill all the information in the "Add connection" dialog. As shown below.
7. You can click "Test connection" button to test connection. As shown below
8. Expand stored procedure folder under the SQL server name and double click the stored procedure which you want to debug for e.g. "addimg", Now Double clicking it will result in displaying the script of the stored procedure. Then put the breakpoint in your desired location in the script. As shown below
9. Now right click the same stored procedure and select "Step into Stored procedure" from the context menu which appear on right click, As shown below
10. Following "Run stored procedure" dialog will appear in which you need to fill all the input parameters which are declared when the stored procedure is created.
11. Bingo.....Success in debugging the stored procedure