Difference between Stored Procedure and Function?
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) |