Posted by John on 12/26/06 13:38

Hey Robert,

Thank you for your reply! That's pretty good advice and I think I'll do
just that, since it's far less complicated than what I was trying to do
(obviously).


Robert Klemme wrote:
> 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]