I wanted to debug a stored procedure today in T-SQL. Using VS 2008 and SQL Server 2008. I followed the instructions on MSDN but it didn't work. I read the troubleshoot guide but it sounded too complex. A comment by a community member helped me:
"3. In Server Explorer, right-click the database object to be debugged and select Step Into Stored Procedure. Set all the desired parameters and press OK.
4. This opens up the script in the VS2008 debugger, with the code pointer at the first executable line. Set breakpoints and debug your T-SQL as you would any other file in VS2008."
However, Visual Studio was exiting the debug mode because it was constructing a SQL statement with incorrect syntax. I tried to escape some parameters but it didn't work. I created a new stored proc in the database with no params calling target stored proc with hardcoded params. Choosing 'Step Into Stored Procedure' from VS then worked. I could step into the target stored proc.