In this blog I am going to explain the basic difference between Stored Procedure and Function.
·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.