Steps to Debug a stored procedure of SQL server 2008 using Visual Studio 2010



Perform all these steps on a machine with SQL server installed.

  1. Window key + R and Write: compmgmt.msc

    SProc1.gif
     
  2. That will open the Computer Management Console; in the left pane expand tree view from system tools >> local users and groups >> groups. Double click "Administrators" which appears in the right pane, as shown below.

    SProc2.gif
     
  3. On double clicking Administrators the 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 a user click the add button as shown below.

    SProc3.gif
     
  4. After the Add... button is clicked the following dialog appears, in which you give user name and click ok as shown in the dialog box below.

    SProc4.gif
     
  5. Now switch to Visual Studio 2010 and open the "Server Explorer" and then click the "connect to database" button as shown below.

    SProc5.gif
     
  6. Fill all the information in the "Add connection" dialog. As shown below.

    SProc6.gif
     
  7. You can click the "Test connection" button to test the connection as shown below.

    SProc7.gif
     
  8. Expand the stored procedure folder under the SQL server name and double click the stored procedure that you want to debug (for example "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.

    SProc8.gif

    SProc9.gif
     
  9. Now right click the same stored procedure and select "Step into Stored procedure" from the context menu which appears on the right click as shown below.

    SProc10.gif
     
  10. The "Run stored procedure" dialog will appear in which you need to fill in all the input parameters which are declared when the stored procedure is created.

    SProc11.gif
     
  11. Bingo………….Success in debugging the stored procedure

    SProc12.gif


Similar Articles