Function execution

  • Comments posted to this topic are about the item Function execution

    If everything seems to be going well, you have obviously overlooked something.


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

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

    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

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

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

    Urbis, an urban transformation company

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

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


    Matija Lah, SQL Server MVP

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

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


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

  • 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]

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

  • "Scary" is an understatement. 😀


    Matija Lah, SQL Server MVP

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

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

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


    Matija Lah, SQL Server MVP

  • 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 79 total)

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