What is difference between Stored procedure and Functions?
Stored procedures:
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won't return Table
Variables
4) you cannot join SP
5) Can be used to change server configuration.
6) Can be used with XML FOR Clause
7) can have transaction within SP
8) Procedures can be used for
performing business logic
9) Sp takes input, output parameters,
10) Sp cannot be called directly into DML statements
11) PROCEDURE may return one or more values through parameters or may not return at all
12) Procedure can return multiple values (max 1024).
13) Stored procedure returns always integer value by default zero.
14) Stored procedure is precompiled execution plan
15) Procedure cannot be used in SQL queries
Functions
1) Can be used with Select statement
2) Not returning output parameter but returns
Table variables
3) We can join UDF
4) Cannot be used to change server configuration.
5) Cannot be used with XML FOR clause
6) cannot have transaction within function
7) Functions are used for computations
8) Function takes only input parameters.
9) Functions can be called directly into DML statements.
10) A FUNCTION is always returns a value using the return statement
11) A Function returns 1 value only.
12) Whereas function returns type could be scalar or table or table values
13) A function can call directly by SQL statement like select
func_name from dual
14) A Function can be used in the SQL
Queries