Difference between Stored Procedure and Function?

 Updated on October 28, 2014     VIKRAM     576

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)

 

If you enjoyed this post, please like (or) share this article.


Comments