• interesting question and discussion today - cheers

  • meh - I guessed and I missed. Oh well.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Revenant (5/2/2012)You are right. If it were passed by value, SQLS would have to make a clone and pass that, and changes to the set in the procedure would be made only to the passed clone, not to the original set.

    Which is not the case.

    Actually table valued parameters must be declared as READONLY in a stored procedures, which means that no DML operations are allowed on the table. Ref

    Don't know why this restriction exist.

  • SQLRNNR (5/2/2012)

    meh - I guessed and I missed. Oh well.


  • Nils Gustav Stråbø (5/2/2012)

    L' Eomot Inversé (5/2/2012)

    Data Set (with the space) could mean table parameter in T-SQL (in which case the correct answer is "value", there's no way of passing this by reference)

    Isn't a table parameter passed by reference (even if you only specify the variable name) to a procedure?

    I don't know how it's done behind the scenes in SQL Server, but I imagine that some kind of pointer is passed to the procedure, not the actual content of the table variable.

    It may well use a pointer behind the scenes, but it's call by value since the thing passed in can't be overwritten in any way. The terms call by value, call by reference, and call by name have meanings concerned with the semantics of access to the parameter concerned, not the underlying mechanism. The semantics are:

    1) call by value: the object provided by the caller cannot be modified by the target procedure

    2) call by reference: the object provided by the caller can be modified by the target procedure

    3) call by name: the object provided by the is a closure which may depend on other things that can be modified while the called procedure is runnng, and the closure is executed to deliver the current value each time the called procedure accesses it

    In the bad old days call by name in Algol 60 was the basis for Jensen's device, which was quite a useful little trick but generally frowned upon. Some early Fortran compilers implemented call by value if the parameter handed in was a constant, and call be reference otherwise. At some point some confusion arose: some compilers implemented call by value using a pointer but ensuring that the called subroutine couldn't write through it; others implemented it by copying the value in; then someone made the sad mistake of assuming (in an update of one of the former compilers) that if a pointer was passed that must be call by reference, with the result that a subroutine could be written that modified the callers constants, so that this little piece of code gets TRUE in its IF statement instead of FALSE (arithmetically somewhat unsound, as 2 and 11111 differ by about 11109) which depending what the code suggested by "......." is might be something of a catastrophe.


    CALL SILLY(11111)

    IF (11111=2) .......









  • Good question, but in my opinion it required more details to clearly understand what it is about.

    Sad to see it was also copied/pasted from already existing articles.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • good topic for the question

    thank you


  • Easy question. Thanks

  • Good question. Thanks for submitting.

  • :hehe::hehe:

    To get quick answer follow this link:

Viewing 10 posts - 16 through 24 (of 24 total)

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