|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 2,021,
Visits: 5,967
|
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 2:57 PM
Points: 1,695,
Visits: 212
|
|
Interesting question, I got it right, but I don't think it's a glitch in SQL Server 2000 or 2005. in the Books Online (for 2005), it is mentioned in http://msdn.microsoft.com/en-us/library/ms186755.aspx :
ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.
So it is intentional (I mean if it's a glitch, they wouldn't have known it and then put the note there?)
Urbis, an urban transformation company
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475,
Visits: 364
|
|
You're right, Iggy.. its not a glitch from what I can see.
I've faced this problem once before so I knew exactly what the question was about.
You can't pass a larger value ( varchar(13) to a varchar(10) ) and expect it to return anything but a truncated value (of varchar(10) itself ) even though the return specifies ( varchar(12) )
Anyway.. have a great day! :)
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 2:57 PM
Points: 1,695,
Visits: 212
|
|
furthermore, they still carry it over to SQL 2008, so definitely intentional :D
Urbis, an urban transformation company
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 05, 2010 1:38 PM
Points: 197,
Visits: 57
|
|
| I come from the oracle world and to me this was too easy. If you have a variable that is 10 character in length as your input variable, and do nothing other than return the input variable, the variable could not be larger than 10 characters. The other part of the code was just wasted code, you just input @bin and return @bin. I do not see this as being a error in sql server part. I would think that it would have always returned 10 characters, if it did something else in the past then that would have been a error in sql server part.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Friday, February 26, 2010 7:58 AM
Points: 2,857,
Visits: 327
|
|
It seems the function's definition is a glitch...
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 11:09 PM
Points: 192,
Visits: 151
|
|
:D, a difinition glitch, i think that is the right answer....
i haven't worked alot in SQL 2005, but even from my SQL 2000 experience you can't fit anything bigger that the container into the container, so the answer was a matter of logical deduction and I wouldn't expect an error to be thrown on truncating a value.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, February 22, 2010 8:52 AM
Points: 1,114,
Visits: 1,795
|
|
I got this wrong, because I didn't stop and think, as I've actually encountered it in the past and now always declare VARCHAR parameters as larger than I expect.
Of course, what I'd really like is something like C's pointer type (char*) as an arguemnt. I think PL/1 used to be able to declare character array arguments as CHAR(*), which would accept a character arrray of any size. If T-SQL allowed VARCHAR(*) in the declaration, you could declare a UDF which would accept a VARCHAR of any size without truncating it and it wouldn't need updating because the database fields were increased in size.
Derek
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, March 12, 2010 5:24 AM
Points: 1,287,
Visits: 597
|
|
| Seems like logically it should throw an error. This one to me is kind of dangerous, if some data happens to be bigger than expected you could end up with some pretty strange results...
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 1,130,
Visits: 2,107
|
|
I got it wrong, in part because I didn't try it out, but also by relying on the trust I put into the SQL team, who should have thought of this. The heck, this is an invitation to hackers who exploit the EXEC statement in an SP! Colud MS pls put this on their Most Urgent To Fix Bug List? After all, I get a 'Will be truncated' message if I try to insert something into a column that's too narrow, so why should this be treated any differently?
--------------------------------------------------------------------------
The function of good software is to make the complex appear to be simple. (Grady Booch)
|
|
|
|
