There is no SUBSTRING function in my code!

Today I have encountered something really annoying, it took me at least an hour to work out what went wrong.

Let’s say I had the following query in a stored procedure:

select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie bao' as name) a

which returned:

image

Now, let’s pretend that I had decided to change the name delimiter from a space to a semicolon and I had forgotten to update the CHARINDEX function:

select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie;bao' as name) a

which returned an error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

The error message looked pretty straight forward. Look again! That’s right, there was no SUBSTRING function in my code!

When I saw that error message, I searched through my stored procedure for the word “substring” over and over again but to no avail. Just when I was about to give up, I noticed the line number. Why don’t I go to that line and see what was actually causing the error? How did I do that? Easy, I will demonstrate with an example:

use tempdb
go
create proc no_substring
as
 
print 'Blah'
 
select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie;bao' as name) a
 
print 'Blah'

return 0
go

Now run the stored proc:

exec no_substring

and you would expect to get:

Blah
Msg 536, Level 16, State 5, Procedure no_substring, Line 6
Invalid length parameter passed to the SUBSTRING function.

Blah

Note that the code starting at Line 6 generated the error. Let’s go there and find the code:

exec sp_helptext 'no_substring'

image

Now back to the problem at hand, I managed to reduce the code to the query below and was still able to produce the error message:

select left('', (select -1))

and got:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

I need to mention that I was running in SQL Server 2005. Just before writing this post, I tried the same code in SQL Server 2008 and got the following error message instead:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

A new error message was created in SQL Server 2008 which is much more helpful at identifying the cause of the error.