Difference between Stored Procedure and Function in SQL Server

In my previous post I explained Stored Procedure and Function in details. So in this post I wrote the differences between Stored Procedure and Function in sql server. If need to learn Stored Procedures and Function so please see my previous post.

What is Stored Procedure in breif:-
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.

What is Function in breif:-
Function is a database object in Sql Server. Function is compiled and executed every time when it is called. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).


Difference between Stored Procedure and Function:-

Sl.No.	Stored Procedure	Function
1	Return value is optional.	Function must return a value.
2	Procedures can have input/output parameters.	Functions can have only input parameters.
3	Procedures cannot be called from Function.	Functions can be called from Procedure.
4	Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it.	Function allows only SELECT statement in it.
5	Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.	Function can be use.
6	Stored Procedures are precompiled code.	Its compiled every time whenever we call it.
7	Stored Procedures cat not be	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 a Procedure whereas try-catch block.	Can not be used in a Function.
9	We can go for Transaction Management in Procedure.	we can't go in Function.

In the above article I try to explain the difference between Stored Procedure and Function in SQL. I hope its useful to you. Please post your feedback, question, or comments about this article.