Dot Net Stuff

Differences between Functions and Stored Procedures SQL Server


A lot of beginners have confusion about stored procedure and functions in SQL Server. Here, I am explaining the definition and the differences between stored procedure and functions.

What is Stored Procedure ?

We can define "Stored Procedure" is a group of sql statements that has been created once and stored in server database. In another way stored procedure is a pre-compile objects which are compiled for first time and its compiled format is saved and there are executes whenever stored procedure is called. Stored procedures will accept input parameters that's why a stored procedure can be used over network by multiple clients using different input data. Stored procedures will helps to reduce network traffic and increase the performance.

What is Functions in SQL Server ?

Function is not pre-compiled object it will execute every time whenever it was called. Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

Difference between Stored Procedures and Functions in SQL Server

Sr. No.Stored ProcedureFunction
1Returning a value  in Stored Procedure is optional
( Procedure can return zero or n values).
Function must return a value.
2Stored Procedures can have input/output parameters.Functions can have only input parameters.
3Stored Procedure may take o to n input parameters.Function takes one input parameter and it is mandatory.
4Stored Procedures cannot be called from Function.Functions can be called from Stored Procedure.
5Stored Procedure allows SELECT as well as 
DML(INSERT/UPDATE/DELETE) statement.
Function allows only SELECT statement.
6Stored Procedures cannot be used in the SQL 
statements anywhere in the WHERE/HAVING/SELECT
section.
Function an be used in SQL Statements anywhere in the
WHERE/HAVING/SELECT section.
7Stored Procedures doesn't allow us to treat as another
rowset.
Functions that return tables can be treated as another 
rowset. This can be used in JOINs with other tables.
8Exception can be handled by try-catch block in Stored 
Procedures.
Try-catch block cannot be used in a Function.
9Stored Procedures allows Transaction Management.Functions doesn't allow Transaction Management.

Summary: My main focus in this article is to explain the definitions of Stored Procedure and Functions in SQL Server, and the differences between Stored Procedures and Functions in SQL Server. You can find separate articles to understand about Stored Procedure and Functions in more details.

Keen to hear from you...!

If you have any questions related to what's mentioned in the article or need help with any issue, ask it, I would love to here from you. Please MakeUseOf Contact and i will be more than happy to help.

About the author

Anil Sharma is Chief Editor of dotnet-stuff.com. He's a software professional and loves to work with Microsoft .Net. He's usually writes articles about .Net related technologies and here to shares his experiences, personal notes, Tutorials, Examples, Problems & Solutions, Code Snippets, Reference Manual and Resources with C#, Asp.Net, Linq , Ajax, MVC, Entity Framework, WCF, SQL Server, jQuery, Visual Studio and much more...!!!