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
)
AS
SQL Statement/SQL Statements
RETURN
Syntax for Function:-
CREATE FUNCTION functionname
(
Parameter1 datatype=defaultvalue,
Parameter2 datatype
)
RETURNS AS
BEGIN
SQL Statement/SQL Statements
RETURN value
END
Differences:-
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.
*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.