overflowing a local varchar

[Zack Sessions]

If I declare a local variable in a cursors query and append values to
it with successiuve concatenations, how come SQL7 does not report an
error if I concatenate a value to it that will overflow it's declared
size? Specifically,

DELCARE @t varchar(5)

....

SET @t = '12345'

....

SET @t = @t + '1 more'

The concatenation of '1 more' does not signal an error. In fact, you
can do any number of successive concatenations and none of them will
report an error.

Shouldn't SQL7 be reporting an error?

[Shervin Shapourian]

Well, it will report an error if you try to insert a value larger than the
actual field size.

Like this:

create table Tst (c varchar(5))
insert into Tst values('123456' )

If you run it you'll get this result:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

So if the value of your local variable is going to be stored in a table, and
if you are changing this value in a way that it can grow, it's not a bad
idea to use bigger variables and then check the result of INSERT or UPDATE
statements.

Shervin
"Zack Sessions" <zc********@vis ionair.com> wrote in message
news:db******** *************** ***@posting.goo gle.com...

If I declare a local variable in a cursors query and append values to
it with successiuve concatenations, how come SQL7 does not report an
error if I concatenate a value to it that will overflow it's declared
size? Specifically,

DELCARE @t varchar(5)

...

SET @t = '12345'

...

SET @t = @t + '1 more'

The concatenation of '1 more' does not signal an error. In fact, you
can do any number of successive concatenations and none of them will
report an error.

Shouldn't SQL7 be reporting an error?

[Manoj Rajshekar]

Hello,

It will just rim off the excess part without throwing an error. It's
similarly handled in SQL 2k too.

-Manoj

[Erland Sommarskog]

Zack Sessions (zc********@vis ionair.com) writes:

If I declare a local variable in a cursors query and append values to
it with successiuve concatenations, how come SQL7 does not report an
error if I concatenate a value to it that will overflow it's declared
size? Specifically,

DELCARE @t varchar(5)

...

SET @t = '12345'

...

SET @t = @t + '1 more'

The concatenation of '1 more' does not signal an error. In fact, you
can do any number of successive concatenations and none of them will
report an error.

Shouldn't SQL7 be reporting an error?

Maybe it should, but it doesn't. :-)

You get an error if you try to put too much into a table column and
you are running with ANSI_WARNINGS ON. But variables never traps this
error.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp