Function execution

Question

Function execution

Ron McCullough

SSC Guru

Points: 63877
More actions
May 19, 2008 at 9:35 pm

#182585

Comments posted to this topic are about the item Function execution
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 79 total)

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

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

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

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 2

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! 🙂

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 3

furthermore, they still carry it over to SQL 2008, so definitely intentional 😀

Urbis, an urban transformation company

cchart3 SSChasing Mays Points: 634 More actions · Answer 4

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.

Matija Lah SSCertifiable Points: 6429 More actions · Answer 5

It seems the function's definition is a glitch...

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Marius Els Right there with Babe Points: 763 More actions · Answer 6

: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.

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 7

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

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 8

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...

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 9

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?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

colin naylor SSC-Addicted Points: 449 More actions · Answer 10

Not a glitch from sql server's point of view at all. If you truncate a string down to 10 characters in the input parameter then of course it is never going to return any more. And if it did, that would be scary.

Matija Lah SSCertifiable Points: 6429 More actions · Answer 11

"Scary" is an understatement. 😀

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

TraderSam SSCrazy Points: 2931 More actions · Answer 12

What's even scarier is programmers not understanding the data they are working with.

If it was easy, everybody would be doing it!;)

Matija Lah SSCertifiable Points: 6429 More actions · Answer 13

Luckily for humanity, such programmers often find good answers in forums such as this one. 😉

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Giri Duddu SSCommitted Points: 1638 More actions · Answer 14

It is not a glitch....

It is working fine...

As a nature the input is truncated to variable definition length10

So it is returning 10letters only