Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.
|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|
To know more on the User-Defined functions visit the article: User-Defined function.
To know more on the Stored Procedure visit the article: Stored Procedure.