A fine site

Difference between Stored Procedure and User Defined Function in Sql Server

Leave a comment

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.


Leave a Reply

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

You are commenting using your 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