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
---------------------------------------
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
---------------------------------------
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
---------------------------------------
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
---------------------------------------
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
---------------------------------------
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
---------------------------------------
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
|
| Author: Pritom Nandy 28 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 Jas 27 Dec 2012 | Member Level: Silver 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 kulkarni 31 Dec 2012 | Member Level: Diamond 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
|