Function vs. Stored Procedure in SQL Server

Question

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that.

Can some one tell me why?

Answer 1

To complete a task, database objects like the stored procedures and Functions contain a set of SQL statements.

Function: Function is compiled and executed every time it is called. This cannot modify the data received as parameters and function must return a value.

Have a look at this video to understand the FUNCTION in detail.

Stored Procedure:  This is a pre-compiled object as this gets compiled for the first time and its compiled format gets saved as well, which gets executed (compiled code) when it is called.

Refer to this informative video about STORED PROCEDURE

Basic differences between Function and Stored Procedure are as follows:

Function	Stored Procedure
The function always returns a value.	Stored Procedure will not return a value, but the procedure can return “0” or n values.
Functions have only input parameters for it.	Whereas, Procedures can have output or input parameters.
You can call Functions can be from Procedure.	But the vice-versa is not correct. As you can’t call Procedures from a Function.

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

In SQL Server advance differences between Stored Procedure and Functions are as follows:

Stored Procedure	Function
You can use Transactions in Procedure.	But, you can’t Transactions in Function.
By using a try-catch block, an exception can be handled in a Procedure.	Whereas, you can’t use try-catch block in a Function to handle the exception.
You can’t utilize Procedures in a SELECT statement.	But Function can be utilized in a SELECT statement.
The procedure allows as DML(INSERT/UPDATE/DELETE) as well as a SELECT statement in it.	Whereas, Function allows only SELECT statement in it.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT statement.	The function can be used in the SQL statements anywhere in SELECT/WHERE/HAVING syntax.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL training.