Check T-SQL for string length problems
-
Any ideas on techniques or tools to analyze T-SQL for potential string length incompatibilties?
Example would be an INSERT that concatenates data together to put in a VARCHAR(80) field, but does not check to ensure the length of the concatenated data is less than or equal to 80. Or just an INSERT that stuffs the contents of a VARCHAR(100) field into a VARCHAR(80) field. Short of checking every INSERT manually (triggers, stored procs, functions), is there an automated way to check for this kind of problem?
-
This is something you have to do manually. The only exception is when you do an insert or alter table where the server will warn you of a possible data loss. Other than that you'd be be doing that client side. There's nothing in the trigger than can help you do that because the data is already truncated in that step.
-
if you have unit testing in place a quick way to find those is to convert all varchar to char and perform the tests you will find the errors and then convert them back to varchar
* Noel -
How would that work Noeld... I usually just call the sp from the app using a command object... and this where the code fails for me. I don't see how that would work only server side??
-
The purpose was to check the INSERT statements. With varchar you get NO errors it will simply truncate the values. If those columns were declared as CHAR it will bomb out at each of the places in which you try to insert a value with a length longer than the destination. Of course the method is assuming you have unit testing in place.
* Noel -
AAAAhhhhhh!!! unit testing = quality assurance
.Makes more sens now
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply