Well, after a short break let’s start the new year with a nice and short post.
This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.
Basically the problem they were having was that they were using a cursor to update a table and some of the records weren’t updating as expected because they appeared to be truncating somewhere. Not easy to track down in a 12,000 line proc with multiple cursors.
Sadly, as mentioned above, this is a common occurrence which I’ve fallen for myself and it can cause massive problems.
It’s all down to the fact that variables can simply truncate data you pass to them in order to fit their data type without giving you any warning at all.
The behaviour we expect is actually that of a table:
if object_id('dataTypeTable') is not null
drop table dataTypeTable
create table dataTypeTable
insert into dataTypeTable
select top 1 salesOrderID, PurchaseOrderNumber
Quite rightly we get the following error message:
Therefore we find ourselves getting lazy and presuming that SQL will inform us every time it comes across a truncation, but sadly it likes to wait until we’re not looking and then bite us in the rear.
declare @code varchar(1)
select @code =
select top 1 PurchaseOrderNumber
As you can see… no error… in fact it reports complete success despite truncating our data and therefore leaving us with a potential issue further down the line. The difference between declaring a varchar as 24 or 25 characters could mean the difference between correct results and expensive, and unseen, mistakes.
In the case I was looking at the variable declaration was made, then the variable loaded as part of a cursor, and the definition was defined as a varchar(50) when, for just 2 records, it needed to be a varchar(55) and this was causing all manner of problems because the first 50 characters of the record matched a join condition and therefore the output was simply wrong but with no immediate indication of where a problem could have occurred or why.
Therefore this post is just a quick note to remind you to always check your data definitions on variables because SQL won’t… it will simply truncate as appropriate.