Posted by Robert Klemme on 12/26/06 12:08

On 26.12.2006 12:46, John wrote:
> I have a question about dynamically changing the length of a varchar(n)
> field, in case the value I'm trying to insert is too big and will give
> a "truncated" error, but before the error is given! i.e. Is there some
> kind of a way to "test" the length of the field while Inserting the
> value into it, and to have it automatically increase its length to the
> length of the value being inserted, in case the value is too big?
>
> I've been able to do this in a "primitive" way, simply by identifying
> the specific error number in case the value is being truncated, and
> then increasing the length of the varchar(n) field by using the ALTER
> command, and then duplicating the insert statement, but is there a
> standard (shorter) way of doing this?

There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.

Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.

Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.

Kind regards

robert

[Back to original message]