May 2, 2012 at 9:54 am
interesting question and discussion today - cheers
May 2, 2012 at 11:17 am
meh - I guessed and I missed. Oh well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 2, 2012 at 12:15 pm
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 http://msdn.microsoft.com/en-us/library/bb510489.aspx
Don't know why this restriction exist.
May 2, 2012 at 12:27 pm
SQLRNNR (5/2/2012)
meh - I guessed and I missed. Oh well.
+1
May 2, 2012 at 1:31 pm
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.
PROGRAM STUPID
CALL SILLY(11111)
IF (11111=2) .......
.......
END
SUBROUTINE SILLY(X)
INTEGER X
X=2
RETURN
END
Tom
May 2, 2012 at 1:51 pm
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.
May 8, 2012 at 2:32 am
good topic for the question
thank you
Iulian
May 17, 2012 at 1:39 pm
Easy question. Thanks
May 17, 2012 at 8:03 pm
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
October 9, 2012 at 4:06 am
:hehe::hehe:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply