Difference between stored procedure and function

 STORE PROCEDURE:---

Syntax :--CREATE PROCEDURE procedure_name

AS

sql_statement

GO;

Query --

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)

AS

SELECT * FROM Customers WHERE City = @City

GO;

FUNCTION (USER DEFINED FUNCTION)  :--

 

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

RETURN return_datatype

{IS | AS}

BEGIN

   < function_body >

END [function_name];

Where,

• function-name specifies the name of the function.
• [OR REPLACE] option allows the modification of an existing function.
• The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.

	STORE PROCEDURE	FUNCTION (USER DEFINED FUNCTION)
1	Procedure can return 0, single or   multiple values.	Function can return only single value
2	Procedure can have input, output parameters.	Function  can have only input parameters
3	Procedure cannot be called from        function.	Functions can be called from parameters.
4	Procedure allows select as well as  DML statement in it.	Function allows only select statement in it.
5	Exception can be handled by  try-catch block in a procedure	Try-catch block cannot be used in a function.
6	Procedure cannot be utilized in a   select statement	Function can be embedded in a select function.
7	Can perform CRUD operation on database.	Cant perform CRUD operation on database.
8	Can use transactions within Stored Procedures.	Transactions are not allowed within functions.
9	Procedures can't be used in Join clause	A UDF can be used in join clause as a result set.