Stored Procedure vs User Defined Function

Question

Can anyone tell me when to use a Stored Procedure instead of a User Defined Function.

Suppose i have a single select statement (possibly a join query), in which of the above I shall encapsulate it? SP or User Defined Function?

 

Thanks in advance. :)

Answer 1

HI ashish !

Simple differnec between these two ;

You can't execute DML(UPDATE,INSERT,DELETE,EXECUTE) statement inside a function, you can only SELECT data from Functions.

Inside sp's you can UPDATE , DELETE , INSERT , EXECUTE or call another sp and you can still SELECT as well.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks, Hasham

Answer 2

Suppose i have a single select statement (possibly a join query), in which of the above I shall encapsulate it? SP or User Defined Function?

My vote goes to SP

 

UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

• A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn’t have to.
• You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
• A UDF can’t use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
• A UDF can’t change server environment variables; a stored procedure can.
• A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you’ve used proper error handling code.

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

Follow these 2 rules:

1. Using SPs you can modify the database, but cannot use an SP in SELECT statement.

2. Using UDFs you cannot modify the database, but can use a UDF in SELECT statement.

 

For more differences between SP & UDFs check this blog post.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 4

Also check this previous post already discussed on this subject: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/feba220c-4b43-4401-9711-e55198cff39e/

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 5

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) can be joined
4) Can not be used to change server configuration
5) Can not be used with XML FOR clause
6) Can not have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

---

velmurugan.s

Answer 6

Check also this good post by Pinal Dave

http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog