Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are.

Is that true? If not, what are the differences between them?

In addition, I know that stored procedures are only complied once and then executed directly. What about functions?

share|improve this question

closed as off topic by jcolebrand Mar 20 '12 at 14:49

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
discussion on SO: stackoverflow.com/questions/1179758/… –  Shawn Melton Mar 14 '12 at 12:55
    
Hey user8365, I edited your question for clarity. Does it correctly convey your intentions? –  Nick Chammas Mar 14 '12 at 15:49
    
This can be solved by looking at any number of web resources. This does not need the input of a database administrator. –  jcolebrand Mar 20 '12 at 14:50

3 Answers 3

This is wrong: Stored procedures and functions both stores in database; Functions can not contain INSERT or UPDATE commands, they are used ony for compute some values and can be used in SELECT when returns recordset. You can read more about the difference and when to use SPs or functions there: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

share|improve this answer

A function attempts to return something, always, and has several restrictions - for example, you can not have any side effects, so you can't issue DML, call stored procedures, use dynamic SQL, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in older versions, at least in SQL Server 2000 - though there are workarounds for this) defined inside a function.

There are several types of functions, mainly scalar and table-valued functions. Scalar functions can be called inline, e.g.

CREATE FUNCTION dbo.PrettyDate(@d DATETIME)
RETURNS CHAR(10)
AS
BEGIN
    RETURN(SELECT CONVERT(CHAR(10), @d, 120));
END
GO

DECLARE @Date DATETIME = CURRENT_TIMESTAMP;
SELECT dbo.PrettyDate(@Date);

Results:

----------
2012-03-14

Table-valued functions need to be called a little differently, since they act essentially like a parameterized view. For example:

CREATE FUNCTION dbo.DatesInRange
(
  @StartDate DATETIME,
  @EndDate   DATETIME
)
RETURNS TABLE  
AS  
  RETURN (SELECT [date] = DATEADD(DAY, n-1, @StartDate)
    FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.objects) AS x
      WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
  );
GO

SELECT [date] 
  FROM dbo.DatesInRange('20120101', '20120105')
  ORDER BY [date];

Results:

date
-----------------------
2012-01-01 00:00:00.000
2012-01-02 00:00:00.000
2012-01-03 00:00:00.000
2012-01-04 00:00:00.000
2012-01-05 00:00:00.000

A stored procedure does not necessarily return data, but it can return more than one resultset. It can be used to affect data (update/insert/delete) and other side effects, can contain dynamic SQL, can have transactions, can have error handling, do not have restrictions about non-deterministic functions, and can call other stored procedures.

EDIT

I am not quite sure what you mean by "function does not store in database." When you create either a stored procedure or a function, its definition is most certainly stored in the catalog view sys.sql_modules, and a reference to the module is created in sys.objects. There is a separate catalog view for stored procedures (sys.procedures) but there isn't an equivalent view for functions - you can still find those in sys.objects with types such as IF, FN and TF.

I will come back on the compile part of the question.

share|improve this answer
    
You can use GETDATE() inside a function. Indeed doing so forces it to be evaluated per row. The error message for NEWID() indicates the problem is because it is side effecting. –  Martin Smith Mar 14 '12 at 14:20
    
@MartinSmith on what version of SQL Server was that restriction lifted? It certainly wasn't always possible (and probably isn't good to recommend in any case). –  Aaron Bertrand Mar 14 '12 at 14:29
    
They were only introduced in 2000 and the restriction doesn't apply to 2005 so maybe it was a 2000 thing then? Edit: Yep 2000 BOL says Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are ... GETDATE() ... –  Martin Smith Mar 14 '12 at 14:35
    
Sadly I don't have a 2005 box handy, but I seem to recall the restriction was there in 2005 as well. I know for certain it was there in 2000. I'll add an asterisk or something. –  Aaron Bertrand Mar 14 '12 at 14:37
    
Excellent answer –  HLGEM Mar 14 '12 at 15:33

The definitions for the Stored Procedures are Functions are stored in the DB. A function cannot perform DML operations on a DB, whereas an SP can. Hence this may be what you are referring to what stored procedures can 'store' in the DB.

According to what I have found: SPs can be compiled and reused whereas scalar UDF are called anew each time.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.