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