Difference between Stored Procedure and Functions in SQL Server

 

Stored Procedure Function
In Stored Procedure it is optional, it can return zero, single or multiple values Function Must Return Value
Stored Procedures are pre-compile objects and can be executed whenever it is called Function is compiled and executed every time when it is called.
Stored Procedure Can Accept Both Input and Output Parameters Function can have only Input Parameters
Procedures cannot be called from Function Functions can be called from Procedure
Stored Procedure can return multiple parameters Function can return only one value
Stored Procedure can contain permanent or temporary tables. It Can call functions inside, and can be executed by using EXEC statement, not used by SELECT statement Function cannot use permanent or temporary tables and cannot call Stored procedures. It can only use Table Variables, and can be called in SELECT statement
Store Procedure allows all type of DML statements and also Select Statements Functions does not allow DML statements such as Insert, Update, Delete
Stored Procedure Cannot be used in Select Statements Functions can be used in Select Statements like views
Stored Procedure can execute Dynamic SQL Function cannot execute Dynamic SQL
Exception try-catch block can be handled in Stored Procedure Exception try-catch block cannot be used in Function
We can go for Transaction Management in Stored Procedure We cannot go for Transaction Management in Function

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s