TechyCage Technology Releated Articles

Friday, 1 January 2016

Difference between Stored Procedures and Function

Procedures can have input/output parameters for it whereas functions can have only input parameters. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.Functions can be called from procedure whereas procedures cannot be called from function

  1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Stored Procedure
Stored in database in compiled format.
Note: Compiled indicates, Execution plan will be made by SQL at the time it created and stored in DB.
Will compiled at run time
Return type
It can directly return only integers

Return type is not must
It can return any scalar or table

Return type is must
Multiple return values
It can also return more than one values (of any data type) indirectly with the help of out parameters
It won't support out parameters
DML Statements
Can have DML statements.
Cannot have DML statements.
Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.
Stored procedure can execute function.

Cannot be the part of Select query as a column.

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
Function cannot execute stored procedure.

Can be the part of select query as a column.

Functions be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
Exception handling
Can have Try....Catch
Cannot have Try....Catch

Sometimes we face a question, why we can't execute stored procedure inside a function?
1. Stored Procedure may contain DML statements.
2. Function can't contain DML statements.
    So executing Function inside stored procedure will never break rule 1.
    But executing stored procedure inside function may break rule no 2.

So ultimately strict rule is made why SQL team, we can't execute stored procedures inside function.

Advance Difference between Stored Procedures and Function :

  1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
  2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  4. 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.
  5. Functions that return tables can be treated as another rowset. This can be used in Joins with other tables.
  6. Inline Function can be though of as views that take parameters and can be used in Joins and other Rowset operations.
  7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  8. We can go for Transaction Management in Procedure whereas we can't go in Function.

  • Precompiled execution: SQL Server compiles each Stored Procedure once and then re utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
  • Reduced client/server traffic: If network bandwidth is a concern in your environment then you'll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction: Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time.
  • Enhanced security controls: You can grant users permission to execute a Stored Procedure independently of underlying table permissions.
  • They allow modular programming: You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
  • They allow faster execution: Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be re parsed and re optimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
  • They can reduce network traffic: An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.


Post a Comment

Subscribe to RSS Feed Follow me on Twitter!