A lot of beginners have confusion about stored procedure and functions in SQL Server. Here, I am explaining the definition and the differences between stored procedure and functions.
What is Stored Procedure ?
We can define "Stored Procedure" is a group of sql statements that has been created once and stored in server database. In another way stored procedure is a pre-compile objects which are compiled for first time and its compiled format is saved and there are executes whenever stored procedure is called. Stored procedures will accept input parameters that's why a stored procedure can be used over network by multiple clients using different input data. Stored procedures will helps to reduce network traffic and increase the performance.
What is Functions in SQL Server ?
Function is not pre-compiled object it will execute every time whenever it was called. Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.
Difference between Stored Procedures and Functions in SQL Server
| Sr. No. | Stored Procedure | Function |
|---|
| 1 | Returning a value in Stored Procedure is optional
( Procedure can return zero or n values). | Function must return a value. |
| 2 | Stored Procedures can have input/output parameters. | Functions can have only input parameters. |
| 3 | Stored Procedure may take o to n input parameters. | Function takes one input parameter and it is mandatory. |
| 4 | Stored Procedures cannot be called from Function. | Functions can be called from Stored Procedure. |
| 5 | Stored Procedure allows SELECT as well as
DML(INSERT/UPDATE/DELETE) statement. | Function allows only SELECT statement. |
| 6 | Stored Procedures cannot be used in the SQL
statements anywhere in the WHERE/HAVING/SELECT
section. | Function an be used in SQL Statements anywhere in the
WHERE/HAVING/SELECT section. |
| 7 | Stored Procedures doesn't allow us to treat as another
rowset. | Functions that return tables can be treated as another
rowset. This can be used in JOINs with other tables. |
| 8 | Exception can be handled by try-catch block in Stored
Procedures. | Try-catch block cannot be used in a Function. |
| 9 | Stored Procedures allows Transaction Management. | Functions doesn't allow Transaction Management. |
Summary: My main focus in this article is to explain the definitions of Stored Procedure and Functions in SQL Server, and the differences between Stored Procedures and Functions in SQL Server. You can find separate articles to understand about Stored Procedure and Functions in more details.