Day 7: Stored Procedures (Part 1)

Concept	Syntax Summary	Purpose
Basic Procedure	`CREATE PROC sp_Name AS BEGIN ... END`	Saves a block of SQL code to be executed over and over.
Execution	`EXEC sp_Name`	Runs the saved procedure.
Input Parameters	`@ParamName DataType`	Allows you to pass dynamic values (like a specific ID) into the procedure when you run it.

1. What is a Stored Procedure?

While a View is just a saved SELECT query, a Stored Procedure (SP) is a saved batch of SQL statements that can do almost anything. It can include SELECT, INSERT, UPDATE, DELETE, and complex programming logic like IF/ELSE statements.

Why are they so important?

Performance: SQL Server "compiles" and caches the execution plan, making them faster than running raw SQL over and over.
Security: They are the #1 defense against SQL Injection attacks. You can grant a user permission to run an SP without giving them permission to access the underlying tables.
Reusability: Write the complex logic once, and let all applications (web, mobile, desktop) just call the SP.

2. Creating & Executing a Basic Stored Procedure

Let's say we frequently need a report of all active employees. Instead of typing the query every time, we wrap it in a Stored Procedure.

-- Step 1: Create the Procedure
CREATE PROCEDURE sp_GetActiveEmployees
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department 
    FROM Employees
    WHERE Status = 'Active';
END;

Once created, you run it using the EXEC (or EXECUTE) command:

-- Step 2: Execute it
EXEC sp_GetActiveEmployees;

3. Making it Dynamic: Input Parameters

A static procedure is okay, but what if we want to get active employees for a specific department? We don't want to create 10 different procedures for 10 different departments. We use an Input Parameter.

Parameters act like variables. They always start with the @ symbol and require a data type.

-- Creating a Procedure with ONE Input Parameter
CREATE PROCEDURE sp_GetEmployeesByDept
    @DeptName VARCHAR(50)  -- This is our input parameter
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @DeptName; -- We use the parameter in our WHERE clause
END;

Executing with Parameters

When executing, you must provide the value for the parameter:

-- This will run the query for 'IT'
EXEC sp_GetEmployeesByDept @DeptName = 'IT';

-- This will run the exact same query, but for 'HR'
EXEC sp_GetEmployeesByDept @DeptName = 'HR';

4. Multiple Input Parameters

You can pass as many parameters as you need. Just separate them with a comma.

CREATE PROCEDURE sp_UpdateEmployeeSalary
    @EmpID INT,
    @NewSalary DECIMAL(10,2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmpID;
END;

Execution:

-- Updates the salary of Employee 101 to 75000
EXEC sp_UpdateEmployeeSalary @EmpID = 101, @NewSalary = 75000.00;

5. Modifying and Dropping Procedures

Just like tables and views, you use ALTER to change the code inside an SP, and DROP to delete it entirely.

-- Modify existing SP
ALTER PROCEDURE sp_GetActiveEmployees
AS
BEGIN
    -- Added Salary to the select statement
    SELECT EmployeeID, FirstName, LastName, Department, Salary 
    FROM Employees
    WHERE Status = 'Active';
END;

-- Delete the SP
DROP PROCEDURE sp_GetActiveEmployees;