Difference between Stored procedures and User Defined functions[UDF]

Discussion in 'Database' started by shabbir, May 29, 2005.

  1. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    The definition of stored procedure as from WIKIPEDIA

    Stored procedure
    A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

    User-defined function
    A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

    In SQL Server 2000
    User defined functions have 3 main categories
    1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
    2. Inline function - can contain a single SELECT statement.
    3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.
    Differences between Stored procedure and User defined functions
    1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
    2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
    3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
    4. Of course there will be Syntax differences and here is a sample of that
    Stored procedure
    Code:
      CREATE PROCEDURE dbo.StoredProcedure1
      /*
         (
      	  @parameter1 datatype = default value,
      	  @parameter2 datatype OUTPUT
         )
      */
      AS
         /* SET NOCOUNT ON */
         RETURN
      
    User defined functions
    Code:
      CREATE FUNCTION dbo.Function1
         (
         /*
         @parameter1 datatype = default value,
         @parameter2 datatype
         */
         )
      RETURNS /* datatype */
      AS
         BEGIN
      	  /* sql statement ... */
         RETURN /* value */
         END
      
     
  2. sunflowerhot

    sunflowerhot New Member

    Joined:
    Jun 19, 2007
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
  3. Linda_rose

    Linda_rose New Member

    Joined:
    Jun 23, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Where can i find detail information on this topic ?????

    Linda
    <<Link Removed>>
     
    Last edited by a moderator: Jun 23, 2007
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Linda, Please confine your links to signatures only.
     
  5. krishnakishu

    krishnakishu New Member

    Joined:
    Jul 5, 2007
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Hyderabad
    Can i get a link where i can clearly identify the difference between a stored procedure and a function?
     
  6. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Your web browser shows the link.
     
  7. ERUM

    ERUM New Member

    Joined:
    Sep 26, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    I did not get the following statment ....


    UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

    Could any one please more focus on it
     
  8. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    You can have UDF in the condition of where clause.
     
  9. ERUM

    ERUM New Member

    Joined:
    Sep 26, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    i have read some where that significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can

    so my question is that how sp can change server environment or your operating system environment .

    I would be grateful to any one if descibe by any example hwo this happens
     
  10. vikas1234

    vikas1234 New Member

    Joined:
    Aug 21, 2008
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    0
    There is nothing like operating system environment correct me if I am wrong it is just the context environment in which program is executing
     
  11. anand_vanam

    anand_vanam New Member

    Joined:
    Apr 6, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    What is the difference between Functions and Stored Procedures?
    Answer:
    1. Stored procedure supports deferred name resolution: but Function won’t support deferred name resolution.
    2. Stored procedures return always an integer value by default zero. Where as function Scalar (single value or table or row set)
    3. Stored procedures are precompiled execution plan but functions are not.
    4. Functions in sql server can be one of two different types 1.scalar, 2. Table
    Stored procedures are 2 types. 1. Regular sp, 2. Temporary sp
    5. The result of the user defined function can be used with in a sql server statement. Where as the result of stored procedures can’t be used in sql server statements.
    6. Stored procedure allows all functionality provided by a function but the reverse is not true.
     
  12. satyedra pal

    satyedra pal New Member

    Joined:
    Mar 26, 2010
    Messages:
    93
    Likes Received:
    1
    Trophy Points:
    0
    stored procedure is a collection of SQL statements with an given name that's stored in the database in compiled format so that it can be used by more
    programs again and again.Stored procedures improve performance by reducing network traffic and CPU load.It is created to performing a large operation like
    more then one query execute simulteneously.Stored procedures can return result sets.You can use IF,WHILE,LOOP,ITERATION, CASE statements in store procedure

    A user-defined function is a set of SQL statements you can call or set a function by name.user-defined function are similar to procedures,but a
    function returns a value to the operation in which it is called.
    user defined function use the folowing:
    statement
    The SET clause in UPDATE statement
    WHERE clause
    The VALUES clause in INSERT statement
    START WITH, ORDER BY, and GROUP BY clauses
     
  13. webdeveloperr

    webdeveloperr New Member

    Joined:
    May 18, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    nice information
     
  14. khaleek_ahmad

    khaleek_ahmad New Member

    Joined:
    Dec 31, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Procedure can modify the state of Database but Function can not. Function can be called in query but Procedure can not.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice