.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

What is difference between stored procedure and Functions?

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

What is difference between stored procedure and Functions?
 

What is 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


http://vishalnayan.wordpress.com/


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend