Post reply

difference between sp and udf

  • May 2, 2008 at 2:51 am

    #121740

    what is difference between stored procedure and user defined functions

    DBDigger Microsoft Data Platform Consultancy.

  • May 2, 2008 at 6:38 am

    #810031

    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.

  • May 2, 2008 at 6:41 am

    #810034

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

    DBDigger Microsoft Data Platform Consultancy.

  • May 2, 2008 at 7:55 am

    #810083

    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

  • April 20, 2011 at 11:42 pm

    #1314313

    you can visit this link also

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

  • April 22, 2011 at 8:24 am

    #1314888

    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

  • April 22, 2011 at 10:51 am

    #1314947

    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)
    Intro to Tally Tables and Functions

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

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