Function vs. Stored Procedure in SQL Server

Question

Hi,

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when exactly I should use functions or stored procedure instead of one another. They look same to me maybe because I am kinda newbie about that.

Can some one tell me why ?

Thanks in advance.

Answer 1

Functions are computed values and cannot perform permanent environmental changed to SQL Server (i.e. no INSERT or UPDATE statements allowed).

A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

Answer 2

Write a user-defined function when you want to compute and return a value for use in other SQL statements; write a stored procedure when you want instead is to group a possibly-complex set of SQL statements. These are two pretty different use cases, after all!

Answer 3

To decide on when to use what the following points might help-

1. Stored procedures can't return a table variable where as function can do that.

2. You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

Answer 4

User defined functions is an important tool available as a sql server programmer. You can use it inline in a sql statement like so select a, lookupValue(b), c from customers where lookupValue will be an UDF. This kind of functionality is not possible when using stored procedure. At the same time you cannot do certain things inside a UDF. The basic thing to remember here is functions cannot do things that can create permanent change - cannot change data where as a stored procedure can do those. For me the inline usage of UDF is the most important usage for UDF.

Answer 5

Functions and stored procedures serve separate purposes. Although it's not the best analogy, functions can be viewed literally as any other function you'd use in any programming language, but stored procs are more like individual programs or a batch script.

Functions normally have an output and optionally inputs. The output can then be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc) or as a predicate to a SQL Query - e.g., SELECT a, b, dbo.MyFunction(c) FROM table or SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Stored procs are used to bind SQL queries together in a transaction, and interface with the outside world. Frameworks such as ADO.NET, etc. can't call a function directly, but they can call a stored proc directly.

Functions do have a hidden danger though: they can be misused and cause rather nasty performance issues: consider this query:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Where MyFunction is declared as:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

What happens here is that the function MyFunction is called for every row in the table MyTable. If MyTable has 1000 rows, then that's another 1000 ad-hoc queries against the database. Similarly, if the function is called when specified in the column spec, then the function will be called for each row returned by the SELECT.

So you do need to be careful writing functions. If you do SELECT from a table in a function, you need to ask yourself whether it can be better performed with a JOIN in the parent stored proc or some other SQL construct (such as CASE ... WHEN ... ELSE ... END).

Answer 6

SQL Server functions, like cursors, are meant to be used as your last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. Talking about performance is talking about a table with more than 1,000,000 records hosted on a server on a middle-class hardware; otherwise you don't need to worry about the performance hit caused by the functions.

1. Never use a function to return a result-set to an external code (like ADO.Net)
2. Use views/stored procs combination as much as possible. you can recover from future grow-performance issues using the suggestions DTA (Database Tuning Adviser) would give you (like indexed views and statistics) --sometimes!

for further reference see: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

Answer 7

wat u guys r talking about.?.??????

Answer 8

this link is also describes pretty well.

http://searchsqlserver.techtarget.com/tip/Stored-procedures-vs-functions