20 main differences between Stored procedures and Functions in Sql Server

IntroductionIn this article i will explain the difference between Stored procedures and User Defined Functions (UDF's) in Sql Server.

In previous articles i explained the Difference between Delete and Truncate in sql server and Difference between Response.Redirect and Server.Transfer in asp.net and Difference between DataSet and DataTable in asp.net and 15 main Difference between DataSet and DataReader in asp.net  and How to create WCF Service to bind,insert,edit,update,delete from sql server database in asp.net C#.

 This is also one of the mostly asked interview questions. I have tried to list some important  differences between the two.

Stored Procedure:
A stored procedure is a pre-compiled group of Transact-SQL statements .We can say a stored procedure is a prepared SQL code that we save so that we can reuse the code over and over again.  If a repetitive T-SQL task has to be executed within an application, then the best way for it is to create stored procedure.

It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.

You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.

User Defined Function (UDF):
 User Defined Function in Sql Server is a Transact-SQL or common language runtime (CLR) routine that takes
parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.


Difference between Stored procedure and Function

1.       User Defined Function can return only 1 value which is mandatory whereas Stored Procedure can return many values(maximum 1024)

2.       User Defined Function can have only input parameters for it whereas Stored Procedures can have input/output parameters.

3.       User Defined Function can't return multiple result sets where as Stored Procedure can.

4.       User Defined Function can be used in a select statement where as Stored Procedures cannot be.

5.       User Defined Function can be called from Stored Procedure whereas Stored Procedures cannot be called from User Defined Function (except extended stored procedures).

6.       Stored procedures are called independently, using the EXEC or EXECUTE command, while User Defined Functions are called from within another SQL statement.

7.       User Defined Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

8.       Stored Procedure can be used to read and modify data but User Defined Function can only read data.

9.       Stored Procedure allows SELECT as well as DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE in it whereas User Defined Function allows only SELECT statement in it.

10.   Procedures cannot be utilized in a SELECT statement whereas User Defined Function can be embedded in a SELECT statement.

11.   Stored Procedures can return XML data types but User Defined Function can't
12.   Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called. But User Defined Function is compiled and executed every time it is called. 

13.   Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas User Defined Function can be.

14.   Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a User Defined Function.

15.   Stored Procedure allows Transaction Management whereas User Defined Function doesn’t.

16.   Stored procedures can be used to change server configuration settings (in terms of security-e.g. setting granular permissions of user rights) whereas User Defined Function can't be used for this
17.   The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in User Defined Function you can’t perform these commands. 

18.   Normally the Stored procedures are used to process certain task but the User Defined Function are used to compute the values i.e. we can pass some value as input and then it perform some task on the passed value and return output. 

19.   In Stored Procedures we can use temporary tables or table variables to store temporary date but in function we can use only table variable. Temporary tables can not be created in User Defined Function .
20.   User Defined Function can be used as user defined data types in create table but procedures cannot.

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

18 comments

Click here for comments
Anonymous
admin
June 20, 2013 ×

some of your points are repetitive.. but anyways its a good list

Reply
avatar
Anonymous
admin
June 25, 2013 ×

Nice, article

Reply
avatar
Anonymous
admin
July 30, 2013 ×

Good one !!

Reply
avatar
July 30, 2013 ×

Thanks.it is always nice to hear that..

Reply
avatar
Anonymous
admin
September 09, 2013 ×

It is definitely a good source of valuable information. Thanks a lot.

Reply
avatar
September 09, 2013 ×

i am glad to hear that you found my articles valuable..keep reading.

Reply
avatar
Anonymous
admin
September 12, 2013 ×

Really Uesful..

Reply
avatar
September 12, 2013 ×

thanks for appreciating my work..keep reading

Reply
avatar
Anonymous
admin
October 21, 2013 ×

nice job

Reply
avatar
October 22, 2013 ×

Thanks for liking my work..stay connected and keep reading..:)

Reply
avatar
Unknown
admin
January 11, 2014 ×

Good job...!

Reply
avatar
January 11, 2014 ×

Thanks Vinayak Rahate..stay connected and keep reading for more useful updates like this....

Reply
avatar
Ankit Maini
admin
February 11, 2014 ×

great bro, cheers!!!!

Reply
avatar
February 11, 2014 ×

thanks Ankit Maini for appreciating my work..stay connected and keep reading for more useful updates like this..

Reply
avatar
zahid Neaka
admin
June 19, 2014 ×

Its very nice ...for ..and thanks for sharing valuable information..

Reply
avatar
June 21, 2014 ×

Thanks Zahid Neaka for appreciating my work..it is always nice to hear that my articles helped anyone..Stay connected and keep reading..

Reply
avatar
Anonymous
admin
May 14, 2015 ×

Nice Article!

Reply
avatar
ravikumar
admin
January 30, 2016 ×

i got one proble plz help me

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..