How I Set Up Debugging In SQL Server Stored Procedures
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
in a stored procedure that uses dynamically built SQL, I might have the dynamic SQL in this form:
SELECT /*short name*/ Col1 FROM ….
note: if your comment is before the SQL, its actually part of the previous statement.
I don’t put the comment at the end because I am usually returning a list of heavy SQL, and I want to see the name in the visible portion of the SQL text column.
another point: how many people set the Application Name in the connection string? I have always advocated setting this for each major element of your app so you can aggregate data based application.
also consider a debug switch in your app. if debug is set for a specific session, then all calls made by that session have a different AppName, allowing you to trace/extended event only for particular session
Yeah, that’s really helpful when you’re looking at the plan cache, query store, or executing query text.