In this post I will explain the difference between SQL Stored procedure and functions. My previous post Send email to multiple receipent in asp.net.
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 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;
In this post i tried to explain the difference between SQL Stored procedure and functions. My previous post Send email to multiple receipent in asp.net.