Difference Between Stored Procedures and User Define Functions

SPs and UDFs are two most important database objects used every day by thousands of developers round the world. More than that SQL Server itself uses these two database objects very frequently. There are some fundamental differences between these two. Here are some major differences between these two:

 

No Stored Procedures Functions
1 Can be used to read and modify data. Can only read data, cannot modify the database.
2 To run an SP Execute or Exec is used, cannot be used with SELECT statement. Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
3 Cannot JOIN a SP in a SELECT statement. Can JOIN a UDF in a SELECT statement.
4 Can use Table Variables as well as Temporary Tables inside an SP. Cannot use a Temporary Table, only Table Variables can be used.
5 Can create and use Dynamic SQL. Cannot use a Dynamic SQL inside a UDF.
6 Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP. Cannot use transactions inside a UDF.
7 Can use used with XML FOR clause. Cannot be used with XML FOR clause.
8 Can use a UDF inside a SP in SELECT statement. Cannot execute an SP inside a UDF.
9 Cannot be used to create constraints while creating a table. Can be used to create Constraints while creating a table.
10 Can execute all kinds of functions, be it deterministic or nondeterministic. Cannot execute some nondeterministic builtin functions, like GETDATE().
Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

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 )

Google+ photo

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

Connecting to %s