function versus stored proc

I have to repost this, so I apologize if anyone responded. For some reason
the copy I sent is not being refreshed in my newsreader Inbox.
Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIt comes down to what kind of interface you want your clients to have to use
in order to get the data from the database. A stored procedure, to me,
"feels" more like a solid interface (from a client perspective) than does a
T-SQL function. Compare:
EXEC dbo.spr_PhraseCount @.Phrase='x'
to:
SELECT dbo.f_PhraseCount('x')
By using the function, you're exposing a little piece of SQL to the client
(i.e., the SELECT statement), whereas the stored procedure completely
encapsulates it. This is a very small issue in this case, but it might
illustrate the direction that you want to take the entire application in
from a data access point of view. Ultimately, you need to decide how much
you want to couple your client (the application tier) to the database.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
>I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Currently they give you the same result, but if you wanted to change things
slightly stored procedures have more flexibility regarding returning
recordsets
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Thanks Adam for your insightful response.
I had originally posted this but lost the thread in my newsreader. However,
I performed a groups.google.com search and found it. I had received a
response from someone named Tibor Karaszi, a SQL Server MVP, who answered my
exact same post with the very brief and useless reply of "IT depends on how
you wish to use it.". That's all he said, so I thank you for your
elaboration. I have participated in the MS newsgrouips for a long time, and
I wish MS would award the MVP status to people like you who take the time to
give people like me information I can use. Whereas Tibor gave me such
pathetically little information that I can do nothing with, and I wonder why
he even bothered to spend what must have been 10 seconds replying to such a
meaningless question to him (it seems), you have provided me information
that I can use.
Thank you.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> It comes down to what kind of interface you want your clients to have to
use
> in order to get the data from the database. A stored procedure, to me,
> "feels" more like a solid interface (from a client perspective) than does
a
> T-SQL function. Compare:
> EXEC dbo.spr_PhraseCount @.Phrase='x'
> to:
> SELECT dbo.f_PhraseCount('x')
> By using the function, you're exposing a little piece of SQL to the client
> (i.e., the SELECT statement), whereas the stored procedure completely
> encapsulates it. This is a very small issue in this case, but it might
> illustrate the direction that you want to take the entire application in
> from a data access point of view. Ultimately, you need to decide how much
> you want to couple your client (the application tier) to the database.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||Thank you.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:mbCdnaLFI9MmDtXZRVny1A@.bt.com...
> Currently they give you the same result, but if you wanted to change
things
> slightly stored procedures have more flexibility regarding returning
> recordsets
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||I'm sorry to hear that you weren't pleased with Tibor's reply -- he is
generally a great source of knowledge on all things SQL Server related, and
he's helped me many times in these groups and elsewhere.
As for MS awarding MVP to people like me, it does happen -- I've been an MVP
since 2004 ;-)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message
news:%23B59gVWZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader.
> However,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
> my
> exact same post with the very brief and useless reply of "IT depends on
> how
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time,
> and
> I wish MS would award the MVP status to people like you who take the time
> to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder
> why
> he even bothered to spend what must have been 10 seconds replying to such
> a
> meaningless question to him (it seems), you have provided me information
> that I can use.
> Thank you.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> use
> a
> news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> reason
> stored
> @.Phrase
>|||mrmagoo wrote:
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader. However
,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
my
> exact same post with the very brief and useless reply of "IT depends on ho
w
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time, an
d
> I wish MS would award the MVP status to people like you who take the time
to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder w
hy
> he even bothered to spend what must have been 10 seconds replying to such
a
> meaningless question to him (it seems), you have provided me information
> that I can use.
>
There are very few people here who are more helpful than Tibor. I
expect his reply was intended to prompt you for more information.
Please remember that any help you do get is for free. If you want a
guaranteed level of service you'd better go hire someone. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am aware that people here contribute for "free", however MVPs get MSDN,
don't they?, so contributions are not without remuneration. Non-MVPs are the
only ones who truly work for free, and I doubt that an MVP wannabe would get
to be an MVP by answering posts that way.
Your point is well taken, however. I did not mean to offend Tibor, but I did
intend to point out that the reply was not helpful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145642545.679167.247350@.t31g2000cwb.googlegroups.com...
> mrmagoo wrote:
However,
answered my
how
and
time to
why
such a
> There are very few people here who are more helpful than Tibor. I
> expect his reply was intended to prompt you for more information.
> Please remember that any help you do get is for free. If you want a
> guaranteed level of service you'd better go hire someone. :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>