How to find the item in a TSQL Select list that throws an exception?

Question

We have a query with hundreds of functions in the select list.

One of the functions throws an exception.

The error message does not indicate the correct line of the faulty function. It only displays the line number on which the Select statement starts.

Using SSMS v18.12.1

You can replicate this by running the following sql statement:

SELECT 
1,
2,
3,
1/0

Output:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

Obviously the error originates from line 5.

Note: Adding TRY and CATCH blocks do not provide more clues:

BEGIN TRY
    SELECT 
    1,
    2,
    3,
    1/0
END TRY
BEGIN CATCH
    SELECT CONCAT(ERROR_MESSAGE(), ' Line: ',  ERROR_LINE())
END CATCH

Output: Divide by zero error encountered. Line: 2

Answer 1

The error indicates Line 1 in your example because it is set-based query, i.e. one of the functions erroring causes the entire SELECT to error.

It seems that what you want to do is to go through each of the functions one-by-one to check for errors. While I'm normally loathe to recommend cursors, it would seem that this is an option that would allow you to step through each of the functions.