Difference Between Stored Procedures and Functions in SQL Server

Stored Procedure :- Stored Procedure is a Group of Precompiled SQL Statements and save with a specific name and call it by its name only.
If you have a requirement where you need to write same query again and again then you can use this query as a strored procedure and call it by its name.

Function :- Functions in SQL server is a Block of SQL statements that performs a specific task on data. Functions have runtime compilation and execution in SQL server.

Below is the Tabular Representation of the Differences in Stored Procedure and Functions.

Sr.No	Function	Stored Procedure
1	Functions can only have input parameters and it do not have output parameters.	Stored Procedures can have either input or output parameters or both the parameters.
2	Functions can be called from Stored Procedures.	Stored Procedures can not be called from Functions.
3	Functions can be used in Select Statement.	SP can not be used in Select Statement.
4	For exception handling we can not use Try-catch block inside the Functions.	For exception handling we can use Try-catch block inside the SP.
5	Function always return a Single value like Scaler Value or return a table as a single value.	SP can have zero , one or multiple return values.
6	Functions have runtime compilation and execution.	SP is precompiled code.
7	We can not use Transcations in Functions.	We can have transactions in SP.
8	We can use Functions in Join Clause.	We can not use SP in Join Clause.
9	We can not use DML statements in functions or we can say that we can not update data by Functions.	We can do data manipulation using the Stored Procedures.
10	We can not use Temp tables in Functions we can use only table variable in functions.	We can use Temp tables in Stored Procedures
11	Every time when you called functions it will be compiled and not reused the execution plan.	Stored Procedures Reused the execution plan when it is called.

Advantages of Stored Procedures :- Thare are many advantages of using the Stored Procedures few are listed in below.

• Stored Procedures provides the Code Reusability.
• It provides code maintainability.
• It reduces the Network Traffic.
• Avoid the SQL injection attacks.
• It impoves the application performance.
• It has precompiled code so that it increases the DB performance.
• It provides the enhanced security controls on SP execution.

Advantages of Functions :- Thare are many advantages of using the Functions few are listed in below.

• Writting code into functions reduces the line of codes that is very useful in low memory systems.
• Code is scattered into many blocks so we can easily debug it.
• It reduces the Network Traffic.