Differences between Stored procedures and User Defined Functions

Stored Procedure:
A Stored procedure is a set of sql statements with a given name , which is physically stored in database in the compiled form. Once the stored procedure has been “stored”, client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. Stored procedures improve performance by reducing network traffic and CPU load.

[source language=”sql”]
CREATE PROCEDURE SPName
@param1 int,
@param2 int OUTPUT,
@param3 varchar(50) INOUT
AS
BEGIN
—-Sql statemt(s)—-
END
[/source]
User Defined Function(UDF):
User defined function is a database object which encapsulates one or more sql statements for reuse , which can accept zero or more parameters and return either a value or table. User defined fuctions contain useful logic for using another quaries or other database objects like stored procedures.
[source language=”sql”]
CREATE FUNCTION FunName (
@param1 int,
@param2 varchar(50),
)
RETURNS int
AS
BEGIN
Declare @RetVal int
—sql statement(s)—
Return @RetVal
END
[/source]
Differences:

1. Procedures can accept input(default), output and inout type parameters for it.
   Functions can accept only input type parameters.

2. Procedures may or may not return a value or may return more than one value using the OUTPUT and/or INOUT parameters. A procedure may return upto 1024 values through OUTPUT and/or INOUT parameters.
   Function always returns only one value.

3. Stored procedure returns always integer value by default zero.
   Function return type could be scalar or table or table values.

4. Stored procs can create a table but can’t return table.
   Functions can create, update and delete the table variable. It can return a table

5. Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
   Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.

6. Stored procedures are stored in database in the compiled form.
   Function are parsed and conpiled at runtime only.

7. Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
   Function are called from select/where/having clause. Even we can join two functions.

8. Normally stored procedure will be used for perform specific tasks.
   Functions will be used for computing value.

9. Stored procedure allows getdate () or other non-deterministic functions can be allowed.
   Function won’t allow the non-deterministic functions like getdate().

10. In Stored procedures we can use transaction statements. We can’t use in functions.
11. The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
    The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.

12. Temporary tables (derived) can be created in stored procedures.
    It is not possible in case of functions.

13. when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
    In case of functions, T-SQL will stop execution of next statements.