Difference between Stored Procedure and User Defined Function in Sql Server

Sr.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 procedures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables as well 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s