Skip Navigation LinksHome > Categories > Code from a Category

What is difference between stored procedure and Functions?


User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 132
Add Date: 01/02/2012
Here, we will discuss difference between stored procedure and Functions
Stored procedures:

1. Have to use EXEC or EXECUTE
2. Return output parameter
3. can create table but won't return Table Variables
4. you cannot 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. Procedures can be used for performing business logic
9. Sp takes input, output parameters
10. Sp cannot be called directly into DML statements
11. PROCEDURE may return one or more values through parameters or may not return at all
12. Procedure can return multiple values (max 1024).
13. Stored procedure returns always integer value by default zero.
14. Stored procedure is precompiled execution plan
15. Procedure cannot be used in SQL queries

Functions

1. Can be used with Select statement
2. Not returning output parameter but returns Table variables
3. We can join UDF
4. Cannot be used to change server configuration.
5. Cannot be used with XML FOR clause
6. cannot have transaction within function
7. Functions are used for computations
8. Function takes only input parameters.
9. Functions can be called directly into DML statements.
10. A FUNCTION is always returns a value using the return statement
11. A Function returns 1 value only.
12. Whereas function returns type could be scalar or table or table values
13. A function can call directly by SQL statement like select func_name from dual
14. A Function can be used in the SQL Queries

Post a Comment
Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below: 		(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.