T-SQL comes with quite effective functions that help convert from one data type to another. But these can be ineffective if one lacks the knowledge of the results of these functions.

I was adding a debugging mechanism to my sproc by capturing the values of all the parameters. All the values are, by default, converted to varchar data type.

When I started evaluating the values of the parameters in my sproc, I noticed that one of them, which has a data type of varchar(999), has its length truncated to a size of 30 characters.

Then in my code I saw the following conversion:

Declare @newValue varchar(999)
Declare @oldValue varchar(999)

Set @newValue = Convert(varchar, @oldValue)

After troubleshooting my code above, I've later found out that it truncates the length of my initial varchar(999) type variable to a size of 30. Of course, I was not pleased.

To correct the problem, I had to specify the size of the new varchar data type, as in the following code:

Declare @newValue varchar(999)
Declare @oldValue varchar(999)

Set @newValue = Convert(varchar(999), @oldValue)  
-- MUST SPECIFY SIZE AS IN THE ORIGINAL DECLARATION

Furthermore, I did my research over the Internet, and I googled the following: default varchar size sql. It turns out that according to this site from MSDN, http://msdn2.microsoft.com/en-us/library/ms176089.aspx,

if the size is not specified when converting to varchar using Cast or Convert function, the default is 30. These codes were run against SQL 2000 and SQL 2005, and the default on both version is the same.

This is a valuable experience for me. And, I hope other coders who are not aware of the case as I described here will also learn from it.

© 2005 Aries J. Manlig

Absolute Software Corp