In this blog I am going to explain the basic difference between Stored Procedure and Function.
Stored procedures:· Have to use EXEC or EXECUTE.
· Return output parameter.
· You can create table but won't return Table Variables.
· You cannot join stored procedure.
· Can be used to change server configuration.
· Can be used with XML FOR Clause.
· Can have transaction within stored procedure.
· Procedures can be used for performing business logic.
· Stored Procedures takes input, output parameters.
· Stored Procedures cannot be called directly into DML statements.
· Procedure may return one or more values through parameters or may not return at all.
· Procedure can return multiple values (max 1024).
· Stored procedure returns always integer value by default zero.
· Stored procedure is precompiled execution plan.
· Procedure cannot be used in SQL queries.
Functions:· Can be used with Select statement.
· Not returning output parameter but returns Table variables.
· We can join UDF (user defined function).
· Cannot be used to change server configuration.
· Cannot be used with XML FOR clause.
· Cannot have transaction within function.
· Functions are used for computations.
· Function takes only input parameters.
· Functions can be called directly into DML statements.
· A FUNCTION is always returns a value using the return statement.
· A Function returns 1 value only.
· Whereas function returns type could be scalar or table or table values.
· A function can call directly by SQL statement like select func_name from dual.
· A Function can be used in the SQL Queries.