🔹 Stored Procedure
A Stored Procedure is a collection of SQL statements that you save in the database and can execute multiple times.
They can include logic (IF, loops, etc.), accept input/output parameters, and perform actions like inserting, updating, or deleting data.
✅ Syntax:
CREATE PROCEDURE procedure_name (parameters)
AS
BEGIN
-- SQL statements
END;
✅ Example: Stored Procedure to Get Employee Details
-- Creating a Stored Procedure
CREATE PROCEDURE GetEmployeeDetails
@EmpID INT
AS
BEGIN
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE EmployeeID = @EmpID;
END;
🔹 Execution:
EXEC GetEmployeeDetails @EmpID = 101;
🔹 Function
A Function is also a reusable SQL block, but unlike procedures:
- Must return a value (scalar or table).
- Cannot perform transactions like
INSERT,UPDATE,DELETE(except some special cases). - Can be used inside queries.
✅ Syntax:
CREATE FUNCTION function_name (parameters)
RETURNS return_type
AS
BEGIN
-- SQL statements
RETURN value;
END;
✅ Example: Function to Calculate Bonus
-- Creating a Scalar Function
CREATE FUNCTION GetBonus(@Salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @Bonus DECIMAL(10,2);
SET @Bonus = @Salary * 0.10; -- 10% bonus
RETURN @Bonus;
END;
🔹 Usage in Query:
SELECT FirstName, LastName, Salary, dbo.GetBonus(Salary) AS Bonus
FROM Employees;
🔑 Difference Between Stored Procedure & Function
| Feature | Stored Procedure | Function |
|---|---|---|
| Return Type | Can return none, one, or many values (via OUT params) | Must return a single value (scalar or table) |
| Usage in Queries | Cannot be used directly in SELECT | Can be used in SELECT, WHERE, etc. |
| DML Operations | Can perform INSERT, UPDATE, DELETE | Generally cannot (except inline table functions) |
| Transaction Handling | Yes | No |
| Execution | Use EXEC or CALL | Used like an expression in SQL |