Home Archive by category "Debugging"

Category: Debugging

Getting the line sys.sp_executesql was executed on during an error

This is a problem I encountered recently on a question on Stack Overflow but found it interesting enough that I wanted to also share it here. When you are using dynamic SQL, you may have noticed that when an error occurs the line number provided in the error is that of the query in the dynamic batch, not the outer query. Take for example the following example: DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10); SET @SQL = N'DECLARE @name sysname;' + @CRLF + N'SELECT @name = name' + @CRLF + N'FROM sys.tables' + @CRLF + N'WHERE object_id =…

Continue reading

Getting to grips with Dynamic SQL: Debugging

Something that many find difficult with Dynamic SQL is debugging it. When looking at a batch that creates and executes a dynamic statement it can be daunting to understand where exactly the error is happening, or even where the SQL that is generating the error is coming from, as it might be a value that was injected, rather than part of the literal strings. I’ve touched on this before, but to reiterative Formatting is Important. That doesn’t just mean with your statements that are creating the dynamic SQL, it means ensuring that the dynamic SQL you create is well formatted…

Continue reading