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 joinsBatches, 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)