Difference Between Stored Procedure And Function

Stored Procedures

Functions

Stored procedures may or may not return a value

Function should return a value

Stored procedure should be executed by ‘EXECUTE’ command.

Functions should execute by using ‘SELECT’ statement.

It will allow DML (Insert ,Update,Delete) operations.

It won’t allow DML (Insert ,Update,Delete) operations

Store Procedure Can allow Input and Output (Upto 24000)parameters.

Function Can allow Input parameters(Up to1200),

it won’t support for output parameters.

For exception handling we can use ‘Try catch blocks’. We can Perform Error handling Using StoreProcedures.

We cant Perform error handling inside a Function.

Inside a stored procedure we can call all types of ‘Data types’

Inside a function we can’t call ‘Text,Ntext,Image’ data types.

In stored procedure we can call any of the SQL objects like tables, temporary tables, sub queries Etc…

We can’t use Temporary tables inside a function

We can’t  create Triggers and Views inside a stored procedures.

We can’t use Views  inside a function

We can call functions and Child stored procedures (32) inside a stored procedures.

We can’t call stored procedures inside a functions.

Code reusability available in stored procedures.

Code reusability not available in functions.

Stored procedures will prefer Differ name resolution.

Differ name Resolution will not be applicable for functions.

After creating a Stored procedures, it will  create an execution plan.

Function won’t create any execution plans.

Stored procedures can’t be used in Join clause

Function can be used in join clause as a result set.

We can use Table variables inside a Stored procedures.

Here also we can use Table variables inside a Functions.