Differences between stored procedures and Functions

This article will explain the differences between functions and stored procedures.

Stored procedures and Functions are a collection of SQL statements that are processed by the database server.
Syntax for stored procedure:-

CREATE PROCEDURE storedprocedurename
Parameter1 datatype,
Parameter2 datatype=default value,
Parameter3 datatype output
SQL Statement/SQL Statements

Syntax for Function:-

CREATE FUNCTION functionname
Parameter1 datatype=defaultvalue,
Parameter2 datatype
SQL Statement/SQL Statements
RETURN value

1)Functions must return a value. Stored procedure need not be.
2)Stored procedures can be called using EXEC command where as Functions can be called from another SQL Statement.
3)Stored Procedures are precompiled. Functions are not precompiled.
4)Generally Stored procedures are used for executing business logic where as Functions are used to execute computations .
5)Functions can return only one value at a time where as stored procedures can return more than one value at a time.
6)Functions cannot return Images and text.Stored procedures can return all types of datatypes.


Author: Jivani01 Apr 2013 Member Level: Gold   Points : 5

*Store Procedures

(1)A stored procedure is a program (or procedure) which is physically stored within a database.

(2)Procedure can return zero or n values.

(3)Procedure can have Input, Output Parameter.

(4)Procedure allow select as well as DML statement in it

(5)Procedure can not be called from function.

(6)We can go for transaction management in procedure

(7)Procedures can not be utilized in a select statement

*User Defined Functions

(1)A user-defined function is a routine that encapsulates useful logic for use in other queries.

(2)Function returns only one value which is mandatory.

(3)UDF can have only Input Parameters.

(4)Functions allow only select statement in it.

(5)Functions can be called from Procedure

(6)In function We can't

(7)Functions can be embedded in a select statement.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: