Dynamically Changing the length of a varchar(n) field

[John]

Hi Everyone,

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?

Here is my code (I'm working in an ASP environment):

<%
var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"

sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"
On Error Resume Next
conn.Execute sql

If err = -2147217833 Then
Response.Write "Error Recognized Successfully!<br /><br />"

sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &
Len(var_txt) &
") NOT NULL"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write "Error while trying to alter Column:<br
/>" & err & "
= " & err.description & "<br />"
Else
Response.Write "Column altered successfully to: " &
Len(var_txt) &
"<br />"

sql = "Insert Into Table1 (text) Values ('" & var_txt &
"')"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write "<br />Error number 2:<br />" &
err.description &
"<br />"
Else
Response.Write "Now it was added successfully!
HaHa!<br />"
End If

End If

Else
Response.Write "Success."
End If
%>

Thanks in advance!

[Robert Klemme]

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

[John]

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