.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Difference between Stored Procedure and function in Sql Server

Posted By: Deco     Posted Date: September 22, 2010    Points:2   Category :Sql Server
Please list some differences between

Stored Procedure and function.

what we can't do in stored procedure and what we can't do in function.

Responses
Author: Ramesh durai             
Posted Date: September 23, 2010     Points: 5   

Hi,

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

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

3)Procedure allow select as well as DML statement in it whereas function allow 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 can not be utilized in a select statement whereas function can be embedded in a select statement.

Ramesh durai
Author: Syed Shakeer Hussain             
Posted Date: September 23, 2010     Points: 5   

Hi ALwyn,



Check the below link for more details.Really the article explained very clearly.

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/Diff-Proc-Func08062009071904AM/Diff-Proc-Func.aspx





Author: Ramesh P             
Posted Date: November 01, 2010     Points: 5   

I have given additional points for difference b/w stored procedure and function.

Stored procedure will be used for perform specific tasks whereas functions will be used for computing value.

Stored procedure can run independently. It can be executed using EXECUTE or EXEC command. But function can not run independently.

Stored procedures can use all the data types available in sql server. But the function cannot use the ntext, image and timestamp data types as return type.




Regards,


Ramesh



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend