1. Resources
    2. SQL Server

Debug your stored procedure

Posted Date: 30 Jun 2012 |Updated: 30-Jun-2012 |Category: SQL Server |Author: |Member Level: Gold |Points: 50 |


In this article i have explained how to debug your stored procedure with the digramatic representation. Many developer unaware of debugging in Stored Procedure with the help of visual studio SQL server addins, this article really helpful for them

Debug your stored procedure



History


We can debug our .NET application easily using a debugger from debug menu, .NET application has it's own .PDB (Program Debug Database) file that
used to store Debug location and can used to convert MSIL code.
This all about .NET but can we debug our stored procedures ?
The answer is Yes. Let's go through the document to check how to debug stored procedure

Introduction


Many times stored procedure play an important role in development life cycle. Stored procedure are not only responsible for query execution but also
responsible for playing an important logic.
Many project does not have a Business logic layer with them, This role can be play by using stored procedure.
Debug is process markingout code step by step. Here we can debug stored procedure using Visual studio editor without using SQL Management studio

Stepwise cooking


1. The first step is to create a Stored procedure that we need to debug (if you have stored procedure already created then you can skip this step)
here i am creating a simple stored procedure for fetching a value from table procedure name is sp_empInfo

Create_procedure


---------------------------------------

2. After creating stored procedure we need to open it through Microsoft Visual studio
Microsoft visual studio has already install SQL addins so we don't need to use SQL server management studio
- The first step we need to execute is open Visual studio editor
- Click on ViewMenu --> Server Explorer
- In Server explorer Pane right click on Data Connections
- Select Add connection

Add_connection


---------------------------------------

3. Now Add connection window will open we need to enter different parameters in Add Connection window, The parameters are
1. Server Name/IP address : Server name/IP Address where Database is installed
2. Authentication for SQL (Windows/sql authentication) here i use SQL authentication
3. Select or enter database name
4. click on Test connection to check if connection is properly established
5. Click on "OK" button to create connection with database

connection_param


---------------------------------------

4. After connection has been successfully established we need to a data connection will be visible in Server explorer
collapse treeview up to stored procedure and select your stored procedure, in my case i select sp_EMP_INFO

After_Connection


---------------------------------------

5. After selecting desired stored procedure we need to debug it, Right click the selected stored procedure, and click on Step Into Stored Procedure

check following snap

Step_into


---------------------------------------

6. After selecting Step Into Stored Procedure your stored procedure execution screen will open, in which, we can see different parameters list, Data types, Output parameters
value to be pass as input to stored procedure, enter some input to stored procedure and click on ok. In this case i have enter value as 10

check following snap

Run_stored_procedure


---------------------------------------

Yes we can use F10 directly to debug our stored procedure.

We can now go and check each and every parameters of stored procedure using this simple article

Suggestion and Queries always welcome

Thanks
Koolprasad2003


Comments

Author: Pritom Nandy28 Nov 2012 Member Level: Gold   Points : 0

It's a good one. Was not knowing that stored procedures can be debugged in this way. Thanks for sharing this.

Author: Sunil Jas27 Dec 2012 Member Level: Gold   Points : 3

I went till "Step Into Stored Procedure" step explained by you the window appeared same as the screen shots u posted for me as well. But when i pressed "Step Into Stored Procedure" then it showed an error.

It displayed as "unable to access the sql server debugging interface,the visual studio cannot connect to the remote computer a firewall maybe preventing remote communication via dcom to the remote computer"

After this an "exception" result as well displayed with an exception number.

Author: Prasad kulkarni31 Dec 2012 Member Level: Gold   Points : 2

Hello sunil,
It looks that you attempt SQL debugging on a machine where SQL debugging is not enabled.
To enable Debugging on machine with SQL check following link.
http://msdn.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx

disable your windows firewall and then try to debug the application

Author: maruthanayakam21 Feb 2014 Member Level: Silver   Points : 5

1. In Solution Explorer , right-click the TradeDev project and select Add , then Stored Procedure . Name this new stored procedure AddProduct and click Add .
2. Paste the following code to the store procedure.
3. Click the left window margin to add a breakpoint to the EXEC statement.
4. Press the drop-down arrow on the green arrow button in the Transact-SQL editor toolbar and select Execute with Debugger to execute the query with debugging on.
5. Alternately, you can start debugging from SQL Server Object Explorer. Right-click the AddProduct stored procedure (located under Local -> TradeDev database ->Programmability -> Stored Procedures ). Select Debug Procedure… . If the object requires parameters, the Debug Procedure dialog box appears, with a table containing a row for each parameter. Each row in the table contains a column for the name of the parameter, and one for the value of that parameter. Enter values for each parameter, and click OK.
6. Make sure that the Locals window is opened. If not, click the Debug menu, select Windows and Local .
7. Press F11 to step into the query. Notice that the parameters of the store procedure and their respective values show up in the Locals window. Alternatively, hover your mouse over the @name parameter in the INSERT clause, and you will see the Contoso value being assigned to it.
8. Click Contoso in the textbox. Type Fabrikam and press ENTER to change the name variable's value while debugging. You can also change its value in the Locals window. Notice that the value of the parameter is now displayed in red, indicating that it has changed.
9. Press F10 to step over the remaining code.
10. In SQL Server Object Explorer, refresh the TradeDev database node in order to see the new contents in the data view of the Product table.
11. In SQL Server Object Explorer, under the Local node, find the Product table of the TradeDev database.
12. Right-click Product table and select View Data . Notice that the new row has been added to the table.


Regards,
Marudhu...

Author: Umesh Bhosale14 Apr 2014 Member Level: Silver   Points : 0

Hello Prasad Sir
I was not know it is possible to debug Store Procedure. Really
nice Article..



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
    • Name:
    Email:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

      more...

      About dotnetspider.com

      We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

      Quick Links
      General
      Services

      Digital Marketing by SpiderWorks Technologies, Kochi - India. ©