What is the difference between Stored Procedure and Function in SQL Server

Introduction:

In this article i will explain what is the difference between Stored Procedure and Function in SQL Server.

Description:

In previous articles i explained about joins, Batches, and Functions. Now i will explain what is the difference between Stored Procedure and Function in SQL Server.

Difference between Stored Procedure and Function:

Stored ProcedureFunction
1. Stored procedure may or may not return a value.1. Function has to return a value.
2. Stored procedure may return any no.of values.2. Function can return only one value.
3. Stored procedure supports DDL, DML, DQL, and DCL statements.3. Function supports only DQL(i.e., select statements)
4. Stored procedure can not return a table.4. Function can return a table.
5. Stored procedure can call another stored procedure.5. Function can not call another stored procedure, but can call an extended stored procedure.
6. Error handling is allowed in stored procedure.6. Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
7. In stored procedures we can use dynamic SQL or temp tables and Table variables.7. Functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

8. Stored procedure is executed with Exec statement.

Example:

Exec spAdd 10, 20

8. Function is executed with Select statement.

Example:

For Table-valued Function:

select * from dbo.GetEmployeesData()

 For Scalar-Valued Function;

select dbo.Addition(10, 20)

 

  • Created
    Dec 21, 2013
  • Updated
    Oct 03, 2020
  • Views
    2,710
Related Articles
Different types of variables in SQL Server
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?