Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.
To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the article: Stored Procedure.
| Sl. No. | User Defined function | Stored Procedure |
| 1 | Function must return a value. | Stored procedure may or not return values. |
| 2 | Will allow only Select statement, it will not allow us to use DML statements. | Can have select statements as well as DML statements such as insert, update, delete etc |
| 3 | It will allow only input parameters, doesn’t support output parameters. | It can have both input and output parameters. |
| 4 | It will not allow us to use try-catch blocks. | For exception handling we can use try catch blocks. |
| 5 | Transactions are not allowed within functions. | Can use transactions within Stored procefures. |
| 6 | We can use only table variables, it will not allow using temporary tables. | Can use both table variables aswell as temporary table in it. |
| 7 | Stored procedures can’t be called from function. | Stored Procedures can call functions. |
| 8 | Functions can be called from select statement. | Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure. |
| 9 | UDF can be used in join clause as a result set. | Procedures can’t be used in Join clause |
[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity
Good synopsis
in 1st point..function must return a value but in 3rd point function doesn’t support output parameters..can you conclude my question
A parameter is a name (address) you pass to the function. With a stored procedure you could “return” many values. Only one value is returned with a function.
You don’t pass name to parameter, you pass value/data to function via parameter name, a function will receive the data, perform some operating on data it receive and return the result..
Wonderful post on difference between UDF & Stored Procedure. You have written it in a very straightforward. Very easy to understand.
Thanks
Wonderful post. It has been put in a very simple & easy to understand way. Thanks.
nice article bro bt i hv 2 doubt please clear it out… u said sp may or may nt return value bt i think sp always return value which may b 0 or 1.is nt so ? scnd is that we cn use handling in functn yar.just use try catch.bt i m nt able to undrstand why every site tellng me u cn use handlng in sp nt in functn bt i think vise versa true.please clear my doubts.
SUPER.
CONTINUE THE SIMILAR THINGS IN FUTURE ….
Nice work.. Here I have one question , why DML statements are restricted in Function?
good but explanation with examples makes easy understanding
1. when and where we can use the user defined functions instead of stored procedure ?? & one example
2. why introduce functions we have already stored procedures ??
3. where we can use UDF , if there is not possible to use SP ?
very useful
Very good synopsis. I would like to add another difference:-
Function can return a table, but stored procedure cannot