none
Difference Between User Defined Function and Stored Procedure

    Question

  •  

    What is the difference between User Defined Function and Stored Procedure? give with more explanation?
    Wednesday, September 24, 2008 3:04 AM

Answers


  • A stored procedure is a program (or procedure) which is physically stored within a database. 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.

    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.


    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
    Wednesday, September 24, 2008 4:34 AM
  •  

    Adding to what is mentioned in the previous post 

    • Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
    • A stored procedure can have both input and output parameters  whereas UDF can only have input parameters.
    Wednesday, September 24, 2008 7:37 AM
  • Hi Satish,

     

    You best bet is to use the search function on the top right of the forums as this question has been covered off quite a few times now.

     

    Searching: "Differences between function and stored procedure" 

     

    Here is an example resut from this:

     

    http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1865259&SiteID=17

     

    Cheers,

    John

    Wednesday, September 24, 2008 7:52 AM
  • Hm, maybe you can find the answer here: http://technet.microsoft.com/en-us/library/cc966425.aspx
    Monday, August 03, 2009 7:19 AM
  • There can be a lot of differences between a UDF and stored procedure, as the prior link has shown.  Another difference is the UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variable.  Scalar UDFs can also be used in the select list, although not advisable.  Another thing that makes Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan. 

    Note: The optimizer can use statistics and optimize Inline TVFs
    http://jahaines.blogspot.com/
    Monday, August 03, 2009 3:15 PM
  • for better understanding please check this link http://www.dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html
    Thursday, April 21, 2011 5:23 AM
  • What is the difference between User Defined Function and Stored Procedure? give with more explanation?

    UDF has READ-ONLY access to databases.

    You cannot change the database (including tempdb) in any shape or form from a UDF.

    The biggest problem with stored procedure that it does not have table out parameter (it has table-valued in parameter since SQL Server 2008):

    http://www.sqlusa.com/bestpractices2005/selectfromsproc/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012





    Friday, June 24, 2011 3:33 PM

All replies


  • A stored procedure is a program (or procedure) which is physically stored within a database. 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.

    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.


    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
    Wednesday, September 24, 2008 4:34 AM
  •  

    Adding to what is mentioned in the previous post 

    • Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
    • A stored procedure can have both input and output parameters  whereas UDF can only have input parameters.
    Wednesday, September 24, 2008 7:37 AM
  • Hi Satish,

     

    You best bet is to use the search function on the top right of the forums as this question has been covered off quite a few times now.

     

    Searching: "Differences between function and stored procedure" 

     

    Here is an example resut from this:

     

    http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1865259&SiteID=17

     

    Cheers,

    John

    Wednesday, September 24, 2008 7:52 AM
  • Is there any diffrence in compiling and execution plan caching ?
    asharafmail@gmail.com
    Monday, August 03, 2009 6:05 AM
  • Hm, maybe you can find the answer here: http://technet.microsoft.com/en-us/library/cc966425.aspx
    Monday, August 03, 2009 7:19 AM
  • Thanks Paul
    asharafmail@gmail.com
    Monday, August 03, 2009 8:01 AM
  • There can be a lot of differences between a UDF and stored procedure, as the prior link has shown.  Another difference is the UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variable.  Scalar UDFs can also be used in the select list, although not advisable.  Another thing that makes Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan. 

    Note: The optimizer can use statistics and optimize Inline TVFs
    http://jahaines.blogspot.com/
    Monday, August 03, 2009 3:15 PM
  • for better understanding please check this link http://www.dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html
    Thursday, April 21, 2011 5:23 AM
  • This question has already been answered here

    Hope, that helps !

     

     


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Thursday, April 21, 2011 7:12 AM
  • What is the difference between User Defined Function and Stored Procedure? give with more explanation?

    UDF has READ-ONLY access to databases.

    You cannot change the database (including tempdb) in any shape or form from a UDF.

    The biggest problem with stored procedure that it does not have table out parameter (it has table-valued in parameter since SQL Server 2008):

    http://www.sqlusa.com/bestpractices2005/selectfromsproc/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012





    Friday, June 24, 2011 3:33 PM
  •  There are  many different between procedure and function,some of different given below

    1)It is optional that Procedure may return values or may not but Function must return one value either a scalar value or a table.

    2)functon returns table variables, while a procedure can't return a table variable although it can create a table

    3)Functions can be called from procedure.Procedures can not be called from function.

    If you want call procedure from fuction you have firt convert it to system define procedure then you can call procedure from fuctions

    4)You have to use EXEC key work call procedure and You can't call procedure in SQL statement

    but fuction call in select statement

    5)procedure can take both input and output parameters but fuction take only input parameters

     

    6)Exception handling option is there in procedure.You can use try catch block in procedure for error handing but there no such option in fuction

     

    7)TRANSACTION management can possible in procedure but in function not possible

     

    8)You can't use insert,update and delete statement in function but you can use this in procedure

     

    9)procedure are pre compiled format in the database where as Functions are compiled and excuted runtime

    Friday, October 14, 2011 11:30 AM
  • Hi here I have Describe the straight difference between SP and UFD just visit it 

     http://sqldebate.blogspot.in/2012/02/diffrence-between-stored-procedure-and.html

    Wednesday, February 01, 2012 2:32 PM
  • Check following blog post for detailed differences in Stored Procedures (SPs) & Functions (UDFs): http://sqlwithmanoj.wordpress.com/2011/09/21/stored-procedures-vs-functions-difference-between-sp-udf/

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, December 11, 2013 6:37 AM