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?

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

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.

    RETURN(SELECT CONVERT(CHAR(10), @d, 120));

SELECT dbo.PrettyDate(@Date);



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

  @StartDate DATETIME,
  @EndDate   DATETIME
  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

SELECT [date] 
  FROM dbo.DatesInRange('20120101', '20120105')
  ORDER BY [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.


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.


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:



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.

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