Stored procedures and functions

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.

---

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

---

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.