If you are like me when you are developing you create a Debug version of your application and in turn .NET provides you with .PDB files for debugging, that is as far as I know, want to know more about it then take a look at John Robbins' Blog on PDB files: what every developer must know
This step-by-step article explains two ways that you can debug SQL Server stored procedures and the necessary configuration settings and steps for each approach.
A Visual Studio .NET developer can use the Server Explorer to debug SQL Server stored procedures independently of any Visual Studio project, or the developer can step into the code of the stored procedure directly from managed code in a Visual Basic, Visual C#, or Visual J# project.
Debug a stored procedure in standalone mode
1. Open Server Explorer.
NOTE: It is not necessary to add a Data Connection to work with a SQL Server server because SQL Server servers are listed under the Servers node also. You will use the Servers node in the steps that follow; however, you can use a Data Connection to you SQL Server server in the same way.
2. Under the Servers node in Server Explorer, expand the SQL Server machine name, expand the SQL Servers node, expand the SQL Server instance, expand the Northwind database node, and then expand the stored procedures node.
3. Right-click the CustOrderHist stored procedure and then click Step Into Stored Procedure.
4. The Run stored procedure dialog box opens, which lists the parameters of the stored procedure. Type ALFKI as the value for the @CustomerID input parameter and then click OK.
5. In the Visual Studio design environment, a window opens that displays the text of the stored procedure. The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion.
6. In the Output window, the following message is displayed, which indicates successful execution:
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).