Differences Between Stored Procedure and Function in MS SQL

Many people using ms sql have wondered about the differences between stored procedure and function.

Although they both look alike, they actually have different aspects.

Below are the differences between the stored procedure and the function.

Functions

  • Always returns a value.
  • Only "input" takes parameters.
  • It can be called in the Stored Procedure.
  • You cannot use transactions.
  • You cannot use "try-catch" blocks to catch errors.
  • You can use it in SELECT query.
  • You cannot use INSERT, UPDATE, DELETE inside the function.

Stored Procedure

  • It doesn't always have to return a value.
  • Both "input" and "output" can take parameters.
  • Cannot be called within a function.
  • You can use transactions.
  • You can use "try-catch" blocks to catch errors.
  • You cannot use it in SELECT query.
  • You can use INSERT, UPDATE, DELETE in Stored Procedure.


You May Interest

MS SQL Getting List of All Column Names of a Table

MS SQL Compiling a Stored Procedure With a Requery

What is RAISEERROR in SQL Server ?

What are the Authentication Modes in SQL Server ?

Showing Server Active Server Name in MS SQL