Difference between User Defined Funtion and Stored Procedure

Recently, I had been gone through an interview in a top IT MNC company. The interviewer asked me about "What do you think, what is the difference between trigger and stored procedure?". This was a long discussion as I gave a subtle difference between them, but still there were many if and buts.

Then, I thought of sharing my thoughts about the difference because many people have only the basic knowledge to differentiate them. But, when the interviewer wants to check your concepts then he/she would ask many questions. So, you must know the underground concepts. Now, first we get the basic idea about them.

What is stored procedure?

A stored procedure is a set of SQL statements with an assigned name that's stored in the database compiled into a single execution plan. It may or may not return result. Mainly, those SQL statements and logic that need to perform a commonly performed task can be designed and coded in a stored procedure.

CREATE PROCEDURE <Procedure-Name> 
(
   [Input parameters],
   [Output Parameters (If required)]
)
AS
BEGIN
     Sql statement
     Sql statement
     Sql statement...
END

<> - All values inside these tags are mandatory.
[] - All values inside are optional.

What is user-defined function?

A user-defined function (UDF) is also a set of SQL statements with an assigned name and stored in the database in compiled form same like a stored procedure. But, a UDF always returns a value while this is not the case with a stored procedure.

CREATE FUNCTION <Function-Name> 
(
   [Input parameters]
)
RETURNS <INT/TABLE>
AS
BEGIN
     Sql statement
     Sql statement
     Sql statement...
END

<> - All values inside these tags are mandatory.
[] - All values inside are optional.

Below are the points that differentiate a stored procedure from a user-defined function (UDF):

User Defined Function (UDF)	Stored Procedure (SP)
It must return a value. It returns either a scalar value (i.e. integer, double etc.) if it is a scalar function or returns a table value if it is a Table-Valued function.	Stored procedure may or not return values.
A function can't have DML statements like insert, update, delete etc. Only, Select statements are allowed.	It can have select statements as well as DML statements such as insert, update, delete etc.
It has only input parameters and doesn’t support output parameters.	It can have both input and output parameters.
Try-Catch blocks are not allowed within in a UDF.	For exception handling we can use try catch blocks.
Transactions are not allowed within functions.	Stored procedures support transactions.
A UDF can't call a stored procedure.	Stored Procedures can call functions.
A function can be called within a SELECT statement.	A stored procedure can only be called using EXECUTE/EXEC keyword. It can’t be called from SELECT/WHERE/HAVING etc statements.

The above points, normally you give in an interview, but if the interviewer wants to know about your concepts then he/she may ask you the following questions also. Few questions that an interviewer may ask:

• We know that stored procedure can have an output paramter using which we can return a value and act as a user-defined function. So, how do you think that stored procedure is different from user-defined function.
• We know that procedure can return a result set consisting of more than one record. But, a user-defined function can also return a result set (i.e. functions that are Table-Valued functions). So, how is user-defined function is different from a stored procedure?

I'll write further to this post to explain more things to differentiate between stored procedure and user-defined functions.

I hope this article helped you to make things clear. Please feel free to post any comment or question.