HELP!!! sql variable truncation

Question

Post reply

HELP!!! sql variable truncation

mudbuddy11

Old Hand

Points: 306
More actions
October 26, 2010 at 1:29 pm

#240298

is there a way for the following to force an error rather than truncation? set ansi warnings was not the answer btw.
declare @temp varchar(10)
set @temp = '12345678901234567'
print @temp
no matter what, it wont error, and I think it should. Is there a way to do this with a database or server setting?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's a behavior thing.

a local variable, when you assign something too big, gets truncated.

however an error will occur if you try to insert/update to a table, which has a more rigid set of rules:

declare @temp varchar(10)

set @temp = '12345678901234567'

print @temp

CREATE TABLE #temp(tempval varchar (10) )

INSERT INTO #temp

SELECT '12345678901234567'

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mudbuddy11 Old Hand Points: 306 More actions · Answer 2

I'm aware of the error when trying to go to a table... I was just wondering if I can set something, so that when I try inserting too large a value to a variable, it will error out.(rather than truncation)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Not directly.

Where does the value come from when you aren't hard-coding it into a sample for this forum?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon