Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Differences between Functions and Stored Procedures

Oct 7, 2012
Introduction:

Here I will explain the difference between stored procedure and function in SQL Server.

Description:

In previous posts I explained difference between Left Join and Left Outer Join, Difference between Len and DataLength and many articles relating to SQL Server. Now I will explain the difference between stored procedure and function in SQL Server.

Stored Procedre:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)

Function:

Function is not pre-compiled object it will execute every time whenever it was called.

Difference between Stored Procedure and Function

1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML(
INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can't go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

If you want more interview questions check this article Interview Questions Series

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Bharti said...
This comment has been removed by a blog administrator.
heroman said...

I do not understand this answer: Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
You can help me an example for above answer. Thanks so much!

rohit said...

Hi, heroman,


U can call store procedure using,

exec procedureName

but u call function using,

select functionName....

Thanga Rajan said...

Such a nice explanation

Prakash Punetha said...

Got Confused After reading below article. Can you please explain?

Ref:
http://msdn.microsoft.com/en-us/library/aa214485%28v=sql.80%29.aspx


CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END

Gaurav said...

very nice tutorial

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.