Post reply

Difference between function and stored procedure

  • November 22, 2005 at 10:22 pm

    #95744

    I would like to know the basic difference between function and stored procedure.

    Thanks & regards

    shakeel

     

  • November 23, 2005 at 5:02 am

    #605428

    From what I know, the only real differences are:

    1.Functions return a single value whereas stored procedures can return multiple.

    2.Non-deterministic functions are not allowed in functions but are for stored procedures.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • November 24, 2005 at 3:19 am

    #605653

    In fact, table valued functions return table, so that means that function can return several values, too. The main difference from user's point of view is that you can use functions in the SELECT clause, and that certain things are not allowed in functions - see BOL for more (one of the most common examples is GETDATE() function).

    For example, if you have a function dbo.CalculatePrice that calculates the sales price based on some customer and product settings, you can write:

    SELECT o.cust_id, o.prod_id, p.prod_name, dbo.CalculatePrice(o.cust_id, o.prod_id)

    FROM Orders o

    JOIN Products p ON p.prod_id = o.prod_id

  • November 24, 2005 at 3:40 am

    #605657

    Table-valued functions may also be used in the from clause

    SELECT blah FROM dbo.SomeFunction(@Param1, @Param2)

    In addition to the restrictions already listed, functions cannot have any side effect. Hence you cannot insert data into a table inside a function or create any object.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2005 at 6:57 pm

    #605776

    Stored Procedure (SP)

  • November 29, 2005 at 1:28 pm

    #606534

    Functions specifically return a scalar or table.  the nice thing is a table value function can be used in your from clause, you just need to alias the result

    select a.a, b.b

    from sometable a inner join (myfunction()) b

        on a.key = b.key

  • April 20, 2011 at 11:42 pm

    #1314314

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

  • April 21, 2011 at 5:51 pm

    #1314760

    In a few words, the difference between sp & functions is that functions yields an expression and so it can be used in a expression context (in SELECT clause, b.e.), whereas stored procedures are commands and cannot be invoked inside expressions.

    With the table returning functions the function shifts into the FROM clause, and you could discuss if it still represents an expression. But still stored procedures are commands, and if you get a resultset from them you cannot use it later; the SELECT is returned to the client (Manager Studio, Query Assistant, etc.) and cannot be used further inside the SQL code (well, you can using INSERT table EXEC sp, but it's a little messy because you must create the table before getting the data, guessing what data types your columns will have).

    Another point of view: stored procedures are scripts: you don't store them as files in your file system but use the same SQL Server as repository giving them a name, similarly as you do with the queries you want to keep saving them with a proper name in the server as Views.

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

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