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.
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.
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:
- 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.
- 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.
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.