SQL Server: Difference between Stored Procedure and Function

 SQL Server     sbo     Updated on Dec 21, 2013     VIKRAM     1137   

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 Procedure

Function

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)

 

  Related Articles
SQL Server: Search for particular keyword in all procedures (or) Get list of procedures where particular keyword or table name is used
SQL Server: Get List of Stored Procedures with Created Date and Modified Date
SQL Server: Triggers
SQL Server: Functions
SQL Server: Named Batches
SQL Server: Anonymous Batches
SQL Server: Batches
  Recent Articles
SQL Server: Search for particular keyword in all procedures (or) Get list of procedures where particular keyword or table name is used
SQL Server: Get List of Stored Procedures with Created Date and Modified Date
SQL Server: Triggers
SQL Server: Functions
SQL Server: Named Batches
SQL Server: Anonymous Batches
SQL Server: Batches