We can debug our .NET application easily using a debugger from debug menu, .NET application has its own .PDB (Program Debug Database) file that used to store Debug location and increment linking of debug configuration. This all about .NET but can we debug our stored procedures? Answer is YES, we can debug our stored procedure. Let's find out How to do it.
Stored procedures are popular enough due to their features like enhanced security, separation is possible in data functionality and application, improved performance (as fewer call made to database), basically it's a one time and one place processing theme. SP (stored procedure) is now more popular in DBA and Developer crowd, But after writing large SP's, how to debug them ? How to test them if they are running properly
So here is the need to Debug stored procedure, Let's see what we need to debug it.
Bit by bit
1. First step is to create a Stored procedure (if you have stored procedure already created then you can skip this step), Here i am creating a simple Stored procedure, which select record from EMP table for specific ID, see below snap
2. After stored procedure created successfully, we are ready to debug it. Basically There are couple of ways to debug stored procedure
Will see how to Debug it using SQL Management studio (I am using SQL 2008R2 Express)
Debug it using Visual Studio
Same way you can use Visual Studio to debug the stored procedure
You can start Debug from now.
Access permission is the biggest issue while debug stored procedure,
Common Error Messages
following are the common error messages occurred during debug stored procedure
see some exceptions/errors snap below
Following exception occurred when you tried to debug Remote stored procedure
Common points to avoid error while debugging
To resolve above errors you can try out following workarounds
What we learn !
We can debug stored procedure by using Visual studio or by using SSMS (Sql management studio), we can use local variables, call stack windows for that. You can provide parameters to stored procedure (if any) and get instant debugger on it.
Hope it will be useful in daily .NET/SQL life.