Function execution

  • Ron McCullough

    SSC Guru

    Points: 63877

    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]

  • Iggy-SQL

    SSCarpal Tunnel

    Points: 4169

    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

    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

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


    Urbis, an urban transformation company

  • cchart3

    Mr or Mrs. 500

    Points: 534

    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

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

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

    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

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

    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

    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

    "Scary" is an understatement. 😀

    ML

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

  • TraderSam

    SSCrazy

    Points: 2931

    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

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

    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

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

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