Difference between stored procedure and user defined functions

Stored Procedure

In our projects we are frequently using the stored procedures and functions.Here I will explain the difference and example.Stored Procedures are set of executable statement which are already pre-compiled and stored in SQL server.Stored procedures no need to return output.We cannot use Stored Procedures in DML statements like (insert/update/delete.In Stored Procedures we can call functions and Stored Procedures inline.Stored Procedures cannot be used in SQL statements like where/having/select section.We can use try/catch block for error handling in stored procedures.

CREATE TABLE  Employees
(
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](200) NOT  NULL
)

Create Procedure Procedure_name 
(
Input parameters ,
Output Parameters (Optional)
)
As
Begin
     Set of Sql statement 
End

//select empname based on empid
Create  PROCEDURE GetEmpName(

@empID INT                    

)
AS
BEGIN
SELECT EmpName FROM Employees WHERE EmpId=@empID 
END

//select all empname names
Create  PROCEDURE GetEmpName()
AS
BEGIN
SELECT EmpName FROM Employees
END

Functions

Functions can be divided into aggregate functions and user defined functions. User Defined functions are stored in SQL server which are compiled and executed by everytime.User Defined functions must return output.We can use User Defined functions in DML statements like (insert/update/delete).In User Defined functions we cannot call Stored Procedures inline.User Defined functions can be used in SQL statements like where/having/select section.We cannot use try/catch block for error handling in User Defined functions.

CREATE FUNCTION udfEmpCount()
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @EmpCount int;
    SELECT @EmpCount = count(EmpName) 
    FROM Employees
     IF (@EmpCount IS NULL) 
        SET @EmpCount = 0;
    RETURN @EmpCountt;
END;

Output
In this post i tried to explain the difference between SQL Stored procedure and functions.