Functions Vs Stored Procedures in SQL Server

Functions and Stored Procedures have few similarities such as:

• They are group of T-SQL statements which is stored on the server.These group of T-SQL statements are executed as a unit.
• Abstracts the developer from the implementation details since he needs to just call the procedure or function.
• Accepts parameters and return values.
• Are used to create reusable logic.This makes the code easy to maintain.

Though Stored procedure and function may seem to be similar,their purpose is quite different.

• Stored procedures may contain statements which modifies the state of the database.Stored Procedures may contain statements such as insert or delete.On the contrary functions are used to just return the data to the caller.Functions can not contain statements such as insert which modifies the existing state of the database.So Insert/Update/Delete statements are not valid in a SQL Server function.

• Stored Procedures can return multiple result sets.On the other hand Functions can only return a single result set.So for returning multiple result sets stored procedure has to be used.

• Stored procedure can call a function.Function can not call a stored procedure.Though a function can call another function or even itself.

• Function can be used in a select statement while Stored Procedure can not be used in a select statement.
  Following function returns all the users in the UserInfo table

CREATE FUNCTION dbo.fnGetAllUsers()
RETURNS TABLE
AS
RETURN
SELECT * FROM UserInfo

fnGetAllUsers() function can be used in a select statement as:

SELECT * FROM fnGetAllUsers()

The ability to use functions in select statement allows developer to break a complex logic into functions.

• Stored procedure can define exception handling logic while Function can not contain exception handling logic such as TRY.. CATCH or RAISERROR.

• Stored Procedures can define transactions whereas Functions do not allow transactions.

• It is mandatory for a function to return a value whereas Stored Procedures might or might not return a value. Though Functions can return different types of data such as a Table or a Scalar value but Functions always return some data to the caller.

     CREATE FUNCTION FunctionName
      (
         parameter list
      )
       RETURNS DataType
         AS

     Function Body

So the RETURN keyword is part of the function definition.

• Stored Procedures are precompiled whereas functions are not precompiled.

Follow on: