I've noticed that SQL Server 2000 seems to truncate parameter values when the value exceeds the parameter's data length—but I don't get an error. For example, if I run the following code: 

CREATE PROCEDURE #test 
  (@data varchar (10))
  AS SELECT @data 
  GO EXEC #test 'This is 
     testing' 
GO 
DROP PROCEDURE #test

I get the following result: 

This is te

Why doesn't SQL Server raise an error?

Although it might seem strange, this feature is working as designed. SQL Server 2000 Books Online (BOL) explains that SQL Server automatically truncates data that's longer than the number of allowed characters. If a column is defined as char(10) and you try to store the value "This is a really long character string" in the column, SQL Server shortens the string to "This is a."

This feature was made before my time (i.e., more than 8 years ago), so I can only speculate that its inclusion in SQL Server 2000 is for backward compatibility with earlier versions.

—Richard Waymire
Group Program Manager
Microsoft Project Team

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

There behaviour is completely as expected. I dont know why this had to become an "Article". If you try to insert the value into a table you would see the expected error:

String or binary data would be truncated. The statement has been terminated.

When you assign the value to the variable it doesnt check for the length unless you initiate an action with the variable. when you print the value it truncates to the allowed length, when you insert into a table, it complains.

ndinakar- February 01, 2006

Article Rating 1 out of 5