Er. alokpandey's Blog

Difference between Function (UDF) and Stored Procedure on MSSQL

Posted in ASP.NET (C# & VB), MSSQL by Alok Kumar Pandey on January 21, 2012

Difference between Function (UDF) and Stored Procedure on MSSQL

Functions

1- Can be used with Select statement
2- Not returning output parameter but returns Table variables
3- You can join UDF
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
7- Can be used in Select list
8- Versioning (grouped) Not Allowed
9- Schema Binding Allowed
10- GETDATE() or other non-deterministic functions Not Allowed
11- SET OPTION Allowed
12- Temp Object Not supported

Stored Procedure

1- Have to use EXEC or EXECUTE
2- Return output parameter
3- Can create table but won’t return Table Variables but possible in MSSQL 2008/2008 R2
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
8- You can not use in SELECT list
9- Compiled, can remember execution plan
10- Schema Binding Not Allowed
11- GETDATE() or other non-deterministic functions Allowed
12- SET OPTION Not Allowed
13- Temp Object Accessible – You can use the temp tables inside the procedure

Reference

Social.msdn.microsoft.com

Advertisements

One Response

Subscribe to comments with RSS.

  1. webcodeexpert said, on May 15, 2013 at 10:50 am

    20 main differences between Stored procedures and Functions in Sql Server
    http://www.webcodeexpert.com/2013/04/difference-between-stored-procedures.html


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

%d bloggers like this: