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