Home > Differences > Stored Procedures vs Functions (Difference between SP & UDF) – SQL Server

Stored Procedures vs Functions (Difference between SP & UDF) – SQL Server

September 21, 2011 Leave a comment Go to comments

Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.

On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.

Following are some common differences between an SP & a UDF:

–> Stored Procedures (SP):
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.

–> Functions (UDF):
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().

More about “User Defined Functions” (UDFs) [check here].

>> Check & Subscribe my [YouTube videos] on SQL Server.

  1. Umayrr
    May 7, 2012 at 7:40 am

    Thanks mate, I’m a web designer trying to develop things now, I was in need of some basics for SQL. This article somehow clarify my doubts concerning UDFs and SPs. Thumbs up.

  2. July 4, 2012 at 8:32 pm

    thanks a lot…

  3. Naveen Dhayal
    August 16, 2012 at 5:05 pm

    Very nice ….Thanks..Big Brother

  4. Ravindra
    August 18, 2012 at 9:19 pm


  5. August 26, 2015 at 5:29 pm

    Little typo here:
    Can use used with XML FOR clause.

  6. nre4ma
    January 2, 2016 at 10:54 am

    I believe GETDATE is allowed in UDFs since 2012. I tried it and it works. Here is the documentation from MSDN. https://msdn.microsoft.com/en-CA/library/ms191007(v=sql.110).aspx

  7. siddu
    October 28, 2016 at 4:24 pm

    please explain this Can only read data, cannot modify the database. for function

  1. September 21, 2011 at 6:34 am
  2. November 19, 2012 at 12:36 am
  3. July 7, 2015 at 2:33 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: