difference between sp and udf

Question

difference between sp and udf

Atif

Hall of Fame

Points: 3578
More actions
May 2, 2008 at 2:51 am

#121740

what is difference between stored procedure and user defined functions
DBDigger Microsoft Data Platform Consultancy.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

There are several differences, but (arguably) the most important difference is that a function is designed to return something other than just an error code.

Atif Hall of Fame Points: 3578 More actions · Answer 2

OK. thanks. Please also tell some more important differences.

DBDigger Microsoft Data Platform Consultancy.

the__t Hall of Fame Points: 3260 More actions · Answer 3

A query on Google will provide you with many links (Search on "differences between stored procedures and user defined functions").

Many of the links I'm unable to access due to filters, but one helpful one is:

Link

"Got no time for the jibba jabba!"
-B.A. Baracus

virat_108 SSC Enthusiast Points: 116 More actions · Answer 4

you can visit this link also

http://dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

AShehzad (5/2/2008)
what is difference between stored procedure and user defined functions

There are so many, and they are so situation-specific, that this open-ended question goes WAY beyond what a forum post is truly for.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

It sounds like an interview or homework question but I'll cough up what I believe are the most important...

A Table Valued Function can be used in the FROM of a query where the output of a stored procedure is much more difficult to use in a FROM clause.

UDF's cannot use Temp Tables.

UDF's cannot modify data in tables except for Table Variables.

UDF's cannot use dynamic SQL.

UDF's can't call other stored proceduresexcept for extended stored procedures.

UDF's cannot return any rows to the screen.

The only way for UDF's to use defaults for parameters is to pass the word DEFAULT to the parameter. They can simply be left out in a stored proc when defaults are assigned within the sproc.

UDF's cannot use indeterminate functions such as NEWID() (although there is a work around for that).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)