tl;dr; Add PRINT and/or SELECT statements. Comment/uncomment out pieces of code to isolate problems. Change INSERT/UPDATE/DELETE statements to SELECTs.

Disclaimer: These are techniques I use all the time. There are almost certainly others. Use what you find works best for you.

Debugging stored procedures, functions, even views is something I end up doing quite a bit. Now, if you are a developer you are probably used to being able to step through your code, set watch values to see the contents of variables, etc. And technically, you can do this with SQL Server if you have access to the debugger. Of course, that requires sysadmin access and getting that can be problematic in anything but your own personal box. Not to mention if you have to figure out why this query isn’t pulling the data you expect in production .. well .. you’re out of luck there.

So what can we do instead? I have three major categories of debugging techniques.

Add PRINT and/or SELECT statements

This is a great way to find out what’s in a variable, or maybe what the query for an EXISTS statement is going to return. It’s also a great way to see which paths your code is taking. PRINT vs SELECT is pretty subjective and frequently you can use either or.

Kind of a contrived example, but let’s say we need to know what’s going on here.

DECLARE @MyDate datetime = getdate(); DECLARE @MyCounter int = 0, @MaxCounter int = 100; WHILE @MyCounter <

Continue reading on SQLStudies.com.