What is 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?
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.