Using SUBSTRING based on line number instead of character offset

This is just a quick little script I had to write recently, and wanted to share around because I thought it was neat and useful.

Problem

Suppose we’re running one or more dynamic SQL commands in SQL Server, and we’re getting an error from somewhere within it.

Let’s suppose, also, that we have a wrapping TRY..CATCH block, and we know the line number where the error has occurred.

But, to make things interesting, let’s suppose that we’re running a large number of such dynamic SQL commands within a loop, each constructed a bit differently, and we’re finding it difficult to pinpoint which of the dynamic commands is causing the error, and which SQL statement it is exactly.

Solution

Assuming that, while we’re inside the CATCH block, we still have the dynamic command stored in a variable, and we know the ERROR_LINE which caused the error, we should be able to use SUBSTRING to find the culprit line within the dynamic script.

It will require a bit of trickery, though, because the native SUBSTRING function is based on individual character offset, rather than line numbers.

Our script would look something like this:

BEGIN TRY
   DECLARE @CMD nvarchar(max), @SubExecution BIT
   -- < some code here >
   WHILE ...
   BEGIN
       SET @SubExecution = 0
       -- < some loop code here constructing the dynamic command >

       -- Actual execution of the dynamic command starts here
       SET @SubExecution = 1

       EXEC sp_execute @CMD

       SET @SubExecution = 0
       -- Execution of the dynamic command ends here

       -- < some other loop code >
   END
   -- < some other code >
END TRY
BEGIN CATCH
	DECLARE @ErrNum INT = ERROR_NUMBER(), @ErrLine INT = ERROR_LINE(), @ErrDesc NVARCHAR(MAX) = ERROR_MESSAGE();

	RAISERROR(N'Error %d in line %d: %s', 16, 1, @ErrNum, @ErrLine, @ErrDesc) WITH NOWAIT;

	-- If error was captured while inside dynamic command
	IF @SubExecution = 1
	BEGIN
		-- Find the offset based on the error line
		DECLARE @CurrLine INT, @CurrStartIndex INT
		SET @CurrLine = 1
		SET @CurrStartIndex = 1

		WHILE @CurrLine < @ErrLine AND @CurrStartIndex < LEN(@CMD) AND @CurrStartIndex > -1
		BEGIN
			SET @CurrStartIndex = CHARINDEX(CHAR(10) COLLATE database_default, @CMD COLLATE database_default, @CurrStartIndex + 1)
			SET @CurrLine = @CurrLine + 1
		END

		-- Print the first 200 characters of the relevant line
		PRINT 'Error occurred inside dynamic command, starting at line ' + CONVERT(nvarchar, @CurrLine) + N', offset ' + CONVERT(nvarchar,@CurrStartIndex) + N':'
		PRINT N'=============================================='
		PRINT SUBSTRING(@CMD, @CurrStartIndex, 200) + CASE WHEN LEN(@CMD) > @CurrStartIndex + 200 THEN N'...' ELSE N'' END
		PRINT N'=============================================='
	END
END CATCH

There are two tricks at play here:

1. Inside the TRY block, we need to use a Boolean variable that would let us know whether the current exception happened inside a dynamic command or not.
2. Inside the CATCH block, if the exception happened inside a dynamic command, we will use a loop and the CHARINDEX function, in order to find the character offset of the relevant error line, and then use that offset inside the regular SUBSTRING function to print just the relevant code section.

Summary

The problem described here is relatively simple, and the solution offered is equally simple:

Use a WHILE loop with CHARINDEX to find the character offset of the relevant line number, and then PRINT with SUBSTRING using the character offset we’ve found.