6

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.

Drew
  • 29,895
  • 7
  • 74
  • 104
satya
  • 1,889
  • 10
  • 29
  • 42
  • 1
    When you ran your performance measurements, what did you learn? Please update the question with actual results you actually got comparing a function and a procedure. – S.Lott Jun 25 '10 at 11:15
  • As i have many stored procedures, i would like to check, if there is any performance issue to have so many? question is edited. – satya Jun 25 '10 at 13:37
  • When you created an EXAMPLE function and an EXAMPLE stored procedure and ran both of these EXAMPLES 1000 times to see the performance difference, what did you learn? Rewriting all 50 in both forms is *not* helpful. Running two EXAMPLES is very, very helpful. – S.Lott Jun 25 '10 at 20:41
  • Please UPDATE your question with the new facts. Please do not add comments to a question which you own. You can update your question to be better and more complete. – S.Lott Jun 28 '10 at 20:28

2 Answers2

2

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

ratty
  • 13,216
  • 29
  • 75
  • 108
1

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.

Samiksha
  • 6,122
  • 6
  • 29
  • 28