To debug a tsql stored procedure using visual studio:
Steps:
1. Make sure sql server is selected in list of debuggers in your project.
Right click on your project (not solution) > select Properties > Click on Web > Debuggers > check SQL Server
2. Open Server Explorer:
VS > View menu > Server Explorer
a. Right click on Data Connections > Add Connection, or Click on 'Connect to Database' shortcut > choose Data Source: Microsoft Sql Server
b. Select 'Server name' (if local, just use '.'), check 'Use Windows Authentication', select database
c. Test connection, if okay, then click OK.
3. Open SQL Server Object Explorer.
If you don's see your sql server listed here, then:
right click on 'SQL Server' > Add SQL Server...
4. In SQL Server Object Explorer > Right-click on your SQL Server and make sure Application Debugging is selected.
You will not be able to step into tsql if this is not selected.
5. Navigate to your sproc in 'sql server object explorer', and double click on it to open it in tsql editor, and add your breakpoint(s).
Make sure you add breakpoint(s) to the sproc from Sql Server Object Explorer', otherwise the breakpoint will not hit: 'The breakpoint will not currently be hit'.
6. Run the project in Debug mode, ie: run debugger (F5). Program will stop in sproc breakpoints.
Open and observe visual studio Debug 'Locals' window to see the value of variables in the stored procedures.
Press F11 to step into.
For more information, see here.
To debug sproc in ssms, see here.
