Difference between stored Procedure and Function

Question

Dear all I just not understanding difference between Stored procedure and function and What is the advantage when we using Stored procedure instead of Tables. Please explain in detail. Thanks & Regards Muniyan

Sam Hobbs · Answer

Thomas can we please avoid speculating about this and wait for Muniyan to provide more information?

Thomas · Answer

Sam in SQL Server you can write stored procedures OR functions. I'm pretty sure that the original question was asking about the difference between them. If you open a database in SQL Manager and expand the programmability folder there is one for stored procedures and a different folder for functions.

Sam Hobbs · Answer

Thomas the question is asking the  difference between Stored procedure and function . That question does not make sense if it is asking about functions that are (in) stored procedures. Then it asks something about  Stored procedure instead of Tables . Then the question asks for details. I think the person seeking assistance should provide more details of what they need help with. How can we do that? How can we get the person to clarify what they need? If Muniyan intends to ask about functions in stored procedures then I want Muniyan to explain that. Unfortunately what often happens when someone asks vague questions is that the person does not clarify the question but other people proceed to create a long discussion of what the person might be asking. I hope Muniyan clarifies this question.

Thomas · Answer

Actually Sam there are functions' in SQL server. In addition to the differences above one of the biggest advantages of a function is that you can query it like a table. With a Stored Proc you get the resulting record set (if there is one) but you can't use it in a query to just return select data from it. A function you can. I often use Functions to Pivot data from a table that as a type code and value column into a table that has the types as fields and the values as rows. i.e. I'll have a sales rep and a customer service rep and a table that has the client and a row for the sales rep and a row for the CS rep. then I will use the function to pivot the table so I can query the clients and have a column for the CS reps and a column for the Sales reps. This allows me to select certain clients for various reports.

Sam Hobbs · Answer

Stored procedures were designed during the early design of SQL Server and existed many years before .Net. So stored procedures use older syntax but exist in the database and are executed in the server. There is nothing specific called function. I assume you mean a function in a client application such as an exe file developed with C#. Applications like that usually escecute in the client computers and usually require that data be sent from the server to the client for processsing which can require a noticeable amout of time for the data transfer and the amount of data can be excessive.

Pravin More · Answer

hello Muniyan some differences is sprocs and functions are....... 1. main thing is:- in functions we can just write select statement. but is stored procedures we can write any statments insertupdate etc which is not allowed in function. it means functions cannnot change ANYTHING. 2.you can make join in 2 functions but not in 2 sprocs. and main advantage of using procedure intead table is.......take exaple of your .net application...suppose you have written query on tables in .net code itself and after application deplyoed you need to change your query then you have to change in .net code and then redeploy .net app.......................but if you use sproc instead query you just need to change in sproc and thats it it will refelct directly in you live app.........!! Thanks Pravin. :)

Senthilkumar · Answer

Hi I have written nice article with complete difference between stored procedure and functions. http://www.c-sharpcorner.com/uploadfile/skumaar_mca/differences-between-procedures-and-functions/ The advantages of using stored procedure is the following. I have written another article for stored procedure practices and advantages. You can go through this link http://www.c-sharpcorner.com/UploadFile/skumaar_mca/good-practices-to-write-the-stored-procedures-in-sql-server/ Advantages of the stored procedure It will improve the execution. It will compile once and be stored as an object under the database. The same execution plan will be used for all the subsequent execution. It will reduce the network traffic. It will have only a few bytes of parameters. But the inline query will make more traffic. It will be stored in the SQL Server database and any number of applications which return in the multiple languages/application can utilizes the same stored procedure. The permission can be set to the stored procedure. The stored procedure can be changed the logics without redeploying the application in circumstances. The application developers can work in the front end and the SQL developers they can write the SQL Server.