SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

We Are TOTALLY Amused: A T-SQL Error

This has to be the best error I’ve ever seen:

Msg 195, Level 15, State 10, Line 2
‘SUM’ is not a recognized built-in function name.

Uhm, I’m sorry to contradict you SQL Server, but yes it is. Here’s the code that produced that error:

, SUM(CASE WHEN printDate IS NULL THEN 1 ELSE 0 END AS printed)
, COUNT(*) total
FROM MyTable

The problem with this particular piece of code is, of course, in that SUM line…but it’s not the SUM that’s the problem.  SQL didn’t know what to make of the misplaced column alias “AS printed”, which should go outside the parentheses:

, SUM(CASE WHEN printDate IS NULL THEN 1 ELSE 0 END) AS printed
, COUNT(*) total
FROM MyTable

I don’t have a point, especially, except to say that errors can be somewhat misleading!

Happy days,
Jen McCown


Posted by PaulHunter on 15 August 2011

I've made many fat-finger mistakes like this.  The rule of thumb I follow with SQL error messages is that they are frequently irrelevant and only point to the  nearest line where the compiler got a WTH.

Posted by EFRAINVILLA on 15 August 2011

This is obvlously a bug from the syntax checker that faced an unexpected combination of code.   I recognize however they made an effort to not just giving the classic "Incorrect syntax near %" (boring).  

As programmers we shouldn´t expect perfect compilers that tell you your  exact mistake.   Your finding is interesting anyway, so, thank you.   Regards.

Leave a Comment

Please register or log in to leave a comment.