Difference between Users-define Function (UDF) and Store Procedure (SP)

        Difference between Users-define Function (UDF) and Store Procedure (SP)

Stored procedure

Definition of Store Procedure (SP): Stored procedure is a pre-compile of SQL statements (program or procedure) which is physically stored within a database. Stored procedure contains two type of parameter:-
1. In Put Parameters: - Input parameter means value (or values) passed to the store procedure
2. Out Put Parameters:- Out Put parameter means value(or values) take from the store procedure

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

Definition of 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.
 
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.
        Difference Between users define Function (UDF) and store procedure (SP)
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 (table values) can be treated as another row set. 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. Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
5. Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
6.Stored procedures have out put parameter but user-defined does not have to out put parameter

7. Of course there will be Syntax differences and here is a sample of that
Stored procedure
Code: SQL

  CREATE PROCEDURE dbo.StoredProcedure1
  /*
     (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
     )
  */
  AS
     /* SET NOCOUNT ON */
     RETURN
  

User defined functions
Code: SQL

  CREATE FUNCTION dbo.Function1
     (
     /*
     @parameter1 datatype = default value,
     @parameter2 datatype
     */
     )
  RETURNS /* datatype */
  AS
     BEGIN
      /* sql statement … */
     RETURN /* value */
     END