Difference between Stored Procedure and Function

th     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.

·         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.

Leave Comment