Sql server User Defined Function (UDF) VS Stored Procedure (SP)

UDFs vs. Stored Procedures
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.

Reference

3 responses to “Sql server User Defined Function (UDF) VS Stored Procedure (SP)

  1. Pingback: Notes July 1st to July 25th 2009 « code siblings,adventures,songs and excerpts of my daily work

  2. Nice explanation, can you think of more, or give more examples.

  3. udf can use GETDATE. update your knowledge.

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