About
Functions and Stored procedures are two major components of any database. Both look similar in terms of their SQL format such as both have body, both Functions and Stored Procedures contain a bunch of SQL statements to perform any task, both can have parameters, etc... Even sometimes you may want to create them as an alternative to each other.
that's it becomes more important to understand differences between Function and Stored procedure so that you should be able to decide when to create a function or stored procedure in SQL.
Difference between Function and Stored procedure in SQL server
1. Return Value :
- Function in SQL Server must return a value, while it is optional in Stored procedure, any stored procedure may or may not return any value.
- The function can return only one value whereas the Stored procedure can return 1 or more values.
2. Code Compile :
Function body will compile and execute every time it is called, whereas Stored procedures are pre-compiled code because they have already complied at the time of creation, and are always saved in a Pre-compiled format.
3. Select and DML Operations :
A Function can only perform read operations and cannot be created for DML operations such as insert, update or delete, whereas a Stored procedure can perform both Select and DML(Insert/Update/Delete) operations.
4. Input and Output Parameters :
A Function can have only input parameters which means you can only pass parameters in the function but cannot define any output parameter whereas in the Stored Procedures you are allowed to pass input as well as define the output parameters.
5. Calling Method :
A Function can not call a stored procedure whereas one Stored procedure can always call a Function.
6. Use in Select statement
A Function can be used in the Select statement and can pass any column value as an input parameter whereas you cannot call Stored Procedure a any Select statement.
7. Use in Filter clause
A Function can be used in the Filter clause such as Where/Having/Nested queries whereas you cannot call a Stored Procedure in Filter clause.
8. Use in SQL JOINs
Any Function which returns a table format can be treated as a rowset and can be utilized in SQL joins to get more extended or refined results whereas you cannot use any Stored Procedure in the SQL joins.
9. Commit and Rollback statements(Transactions)
A Function cannot contain commit or Rollback script features which means you can't implement transactions in a Function because it is only use for Select or read statements, you can say Transactions wouldn't be required inside a function whereas you can implement a transactions in a Stored procedure.
10. Exception Handling (Try & Catch block)
A Function cannot have exception handling such as Try & Catch block whereas exception handling can be performed inside the Stored procedure body to avoid any exception/error.
Use cases for Functions and Stored Procedures
I hope you have learned well the major differences between a Function and a Stored Procedure, now another important aspect is to decide when to create a function or stored procedures, there are a few use cases as below -
1. Select and DML Operations
If it is required to perform Insert/Update/Delete operations then you should create a Stored Procedure, because it wouldn't be possible by a Functions as it doesn't support the DML operations.
2. Only Select Statement, as a Method
If it is required only to read the data rows without having the necessity of transactions and exception handling then you should go for a function. The main advantage of using function here is that you can utilize it in any select statement, joins, or in the Filter clause.
Summary
In this article you have learned about the important differences between Functions and Stored Procedures in SQL Server, I hope you would find this article helpful. Any comment/suggestion or feedback is most welcome so that I can improve your reading experience.
0 Comments