Table of Contents

    Difference Between Stored Procedures and Functions in SQL Server: A Clear Comparison

    Rumman Ansari July 21, 2024 6,269 views
    Difference Between Stored Procedures and Functions in SQL Server: A Clear Comparison

    Difference between Stored Procedure and Function

    Stored Procedures:
    Stored Procedures is pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. 
     
    Function:
    Function is not pre-compiled object it will execute every time whenever it was called.
     
    Difference between Stored Procedure and Function
      Stored Procedures Function
    Basic Difference
    1 Stored Procedure it is optional (Procedure can return zero or n values) Function must return single value
    2 Stored Procedures can have input/output parameters Functions can have only input parameters
    3 Functions can be called from Stored Procedure Stored Procedures cannot be called from Function
    Advance Difference
    4 Exception can be handled by try-catch block in a stored procedure Exception try-catch block cannot be used in a function
    5

    StoredProcedure allows SELECT as well as DML(INSERT/UPDATE/DELETE)

    statement in it

    		 Function allows only SELECT statement in it
    6 Stored Procedures cannot be utilized in a select statement Function can be embedded in a select statement
    7

    Stored Procedures cannot be used in the SQL statements anywhere in the

    WHERE/HAVING/SELECT section

    Function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT

    section
    8 We can go for Transaction Management  in Stored Procedure. We cannot go for Transaction Management in Function
    9 Stored Procedure can fire Triggers Function can not fire Triggers.
    10 Stored Procedure can create Table variable but can not return table variable Function can create Table variable and return table variable.
    11 Print command can be use in stored procedure Function can not be use print command.
    12 Store procedure can execute Dynamic SQL Function cant not be execute Dynamic SQL
    13 DML and DDL operation can be performed in SP

    DML and DDL operatoin can be performed in function  but we can use only select statement

    and Insert statement to insert row into table variable
    14 Temporary table can be accessed in stored procedure. Temporary Table can be created in SP Temporary Table Can not be accessed in Function. We can not create Temporary in Function