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 =…