In a Sql database management system (DBMS), a stored procedure is a group of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.
Difference between Sql Stored Procedure and Function:
There are fallowing difference :-
- Sql Functions can have only input parameters but Procedures can have input/output parameters.
- Sql Functions can be called from Procedure while Procedures cannot be called from Function.
- Sql Function can be embedded in a SELECT statement. But Procedures cannot be utilized in a SELECT statement.
- The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
- Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
- Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
- We can go for Transaction Management in Procedure whereas we can't go in Function.
- Stored procedure allows modular programming.
- Stored Procedure can reduce network traffic.
- Stored procedures provide better security to your data.
Next Sql Stored Procedure topic:
- Using input parameters
- Using output parameters
- Using Try Catch