Menu
What are the differences between stored procedures and functions.
Whenever there are more input, output parameters i go for stored procedure. If it is only one i will go for functions.
Besides that, is there any performance issue if i use more stored procedures? I am worried as i have close to 50 stored procedures in my project.
How they differ conceptually.
Thanks in advance!
EDITED:-
When i executed a calculation in stored procedure and in functions, i have found that in stored procedures it is taking 0.15 sec, while in function it takes 0.45sec.
Surprisingly functions are taking more time than stored procedures. May be functions are worth for its reusability.
Inline functions executes quicker than strored procedures. I think, this is because multi-select functions can't use statastics, which slows them down, but inline table-value functions can use statistics.
235
In a function, it is mandatory to use the RETURNS and RETURN arguments, whereas in a stored procedure is not necessary. In few words, a stored procedure is more flexible to write any code that you want, while functions have a rigid structure and functionality.
Stored Procedures is a tool that is used to perform any specific operations like Insert, Update or Delete in our database recursively and it can be used to alter or update any records in database. Unless function that returns only single value, stored procedures can return zero and many values at a time.
In SQL: A Procedure allows SELECT as well as DML ( INSERT , UPDATE , DELETE ) statements in it, whereas Function allows only SELECT statement in it. Procedures can not be utilized in a SELECT statement, whereas Functions can be embedded in a SELECT statement.
A function would return the returning value/control to the code or calling function. A procedure, on the other hand, would return the control, but would not return any value to the calling function or the code. We cannot use the DML statements in a function, (functions such as Update, Delete, and Insert).
Difference between stored procedure and functions in SQL Server ...
http://www.dotnetspider.com/resources/18920-Difference-between-Stored-Procedure-Functions.aspx
Difference between Stored procedures and User Defined functions[UDF]
http://www.go4expert.com/forums/showthread.php?t=329
Stored procedures vs. functions
http://searchsqlserver.techtarget.com/tip/Stored-procedures-vs-functions
What are the differences between stored procedure and functions in ...
http://www.allinterview.com/showanswers/28431.html
Difference between Stored procedure and functions
http://www.sqlservercentral.com/Forums/Topic416974-8-1.aspx
135
To decide between using one of the two, keep in mind the fundamental difference between them: stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.
If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.
There's quite a bit of debate about the performance benefits of UDFs vs. SPROCs. You might be tempted to believe that stored procedures add more overhead to your server than a UDF. Depending upon how your write your code and the type of data you're processing, this might not be the case. It's always a good idea to text your data in important or time-consuming operations by trying both types of methods on them.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
