Functions vs StoredProcedures

From a sp I should call many other sp or functions. There're difference
between SP and Functions about performance? Are functions more performant
then sp.
ThanksProcedures and Functions are not functionaly equivalent. For example,
functions can only perform inserts/updates/deletes on table variables that
it declares. Also, only a function can return a scalar value.
http://msdn.microsoft.com/library/d...edprocedure.asp
http://msdn.microsoft.com/library/d...br />
50mr.asp
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||>> functions can only perform inserts/updates/deletes on table variables
Just to clarify. Functions can be table valued as well. And you can update
permanent tables in a database using a table valued function as well:
CREATE TABLE tbl ( col INT NOT NULL PRIMARY KEY );
GO
CREATE FUNCTION ufn ( @.p INT ) RETURNS TABLE AS
RETURN ( SELECT col FROM tbl WHERE col = @.p )
GO
INSERT ufn(1) SELECT 1 ; SELECT * FROM tbl ;
UPDATE ufn(1) SET col = 2 WHERE col = 1 ; SELECT * FROM tbl ;
DELETE ufn(2) WHERE col = 2 ; SELECT * FROM tbl ;
The restriction is that you cannot perform them on permanent tables from
within the function.
Anith|||FWIW: 'performant' isn't a word.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||On Wed, 28 Sep 2005 11:26:57 -0500, Anith Sen wrote:

>Just to clarify. Functions can be table valued as well. And you can update
>permanent tables in a database using a table valued function as well:
(snip)
Hi Anith,
After running your code, I can't deny that you _CAN_ do this. But this
is not mentioned anywhere in Books Online (in fact, BOL says that
INSERT, UPDATE, and DELETE all operate on a table, a view, or a
OPENQUERY or OPENROWSET rowset-function). I don't think that anyone
should ever rely on this behaviour!
(If I may speculate - I *think* that the reason for this behaviour is
that inline table-valued functions and views have so much in common that
they re-use lots of the same code, and someone at MS forgot to exclude
UDFs in the re-used code for INSERT, UPDATE and DELETE).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually it is a word, but perhaps used out of context:
http://dictionary.reference.com/search?q=performant
"Mark" <a@.b.net> wrote in message
news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
> FWIW: 'performant' isn't a word.
>
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
>|||I don't think that english is his mother tongue.
In French "performant" is an adjective that discribes something that has
performs well.
For the life of me I cannot think of an English word that can be used in the
same way.
My French-English translator doesn't even make a suggestion.
"JT" <someone@.microsoft.com> wrote in message
news:OQKvMtRxFHA.3756@.tk2msftngp13.phx.gbl...
> Actually it is a word, but perhaps used out of context:
> http://dictionary.reference.com/search?q=performant
> "Mark" <a@.b.net> wrote in message
> news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
>|||>> After running your code, I can't deny that you _CAN_ do this. But this is
I tend to agree with the lack of sufficient documentation which can cause
some confusion. For instance, add a DISTINCT to the SELECT clause in the UDF
& you'll see the same limitations of updateable views.
Anith