Top 3 SQL Errors That Will Leave Your Users Stranded

Over the years I’ve ran across certain situations that cause errors in SQL that error out the calling application if they are not correctly trapped. As unsuspecting as these can be, you end up learning your lesson when you get a call late at night that a web page is erroring out. Then to follow you get a deep sinking feeling in your gut while you think, “Why didn’t I think of that?”.

In some of these cases I’ve often wondered why SQL doesn’t simply return a NULL or an invalid result. And often I wish there was an option that could set that because while it’s true that all errors should be handled, in truth they are not.

Hopefully this list can save you a bit of heartache.

• divide by zero
• string or binary data would be truncated
• Invalid length parameter passed to the LEFT or SUBSTRING function

These three errors are very common and all of them at one time or another have bit me in the pants – most of the time after deploying code and even worse, hours/days/months after deploying the code. Not only that, they can occur on an intermittent basis which is always fun to troubleshoot.

Divide by Zero

This innocent division operation turns ugly when your divisor hits the dead even zero. This is because of the rules of math. You simply cannot divide by zero. Reproducing this issue is simple enough.

[cc lang=”sql”]
SELECT 8 / 0
[/cc]

The workaround is to make the divisor NULL, which then returns a NULL
[cc lang=”sql”]
SELECT 8 / NULL
[/cc]

Why does it error out instead of returning NULL? While I’m not sure I’d imagine if paychecks were being calculated it’d be better to have the code error out so someone would catch it than have your “Pay Amount” equal NULL. Though this is the case there are times in the past I wish it did return a NULL. Luckily there is a workaround. Overcome Divide by Zero Using NULLIF

String or Binary Data Would be Truncated

Ah yes, the dreaded string or binary data would be truncated error. Even if you have been coding for a month or so I’m sure you’ve run into this one. When this error crops up in a SQL statement that has a large SELECT list, you have to painstakingly comment out half of the columns (in b-tree fashion) to end up narrowing down which column is causing this error.

In the majority of cases when I’ve come across this error, it’s because a predefined table did not have a datatype large enough to accommodate the unsuspecting data. This can very simply be reproduced using the following example.

[cc lang=”sql”]

DECLARE @target TABLE
(
mybin varbinary(2),
mystring varchar(2)
)

INSERT INTO @target (mybin, mystring)
SELECT
mybin = 512
,mystring = ‘hiu’
[/cc]

Gives us:

In this particular case, maybe we didn’t know what size the data would be before we inserted it. If that’s the case we can select..into a temp table instead. More info here.

Invalid length parameter passed to the LEFT or SUBSTRING function

Ah yes. Once again we have a doozy. You’ve created your complex string parsing function only to find out you didn’t account for the string not being there (At least that’s where I run into this the most).

To reproduce this error we can do
[cc lang=”sql”]
SELECT LEFT(‘hello’, -1)
[/cc]

Of course it makes sense we cannot return the negative one character from the string, and I sometimes wonder why it doesn’t return a NULL. But there’s probably a reason that I don’t know about.

For simplicity, the above example doesn’t reproduce the exact error mentioned. To reproduce that error, we can do the following:
[cc lang=”sql”]
DECLARE @MyString varchar(50) = ‘no slash here’

SELECT SUBSTRING(LEFT(@MyString,CHARINDEX(‘/’, @MyString )-1),4,1)
[/cc]

Then to fix, we can implement the CASE statement:
[cc lang=”sql”]
DECLARE @MyString varchar(50) = ‘no slash here’
SELECT SUBSTRING(LEFT(@MyString,CASE WHEN CHARINDEX(‘/’, @MyString )-1 < 0 THEN 0 ELSE CHARINDEX('/', @MyString ) -1 END),4,1) [/cc] So remember if you can, when running into these scenarios, make sure to account for the errors that can happen.