Today I Learned

Stored Procedures are precompiled code blocks that reside within a database. They provide a way to encapsulate and reuse frequently executed SQL statements, improving performance, maintainability, and security.

Benefits of Stored Procedures:

Performance: Precompiled code executes faster than executing statements directly.
Modularity: Encapsulate complex logic, making code more organized and reusable.
Security: Centralize security rules and permissions.
Data validation: Enforce data integrity and consistency.

Example:

CREATE PROCEDURE GetCustomers
AS
BEGIN
    SELECT CustomerID, CustomerName, City
    FROM Customers;
END;

// How to use?

EXEC GetCustomers;

Example using params:

CREATE PROCEDURE GetCustomersByCity
    @City nvarchar(50)
AS
BEGIN
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE City = @City;
END;

// How to use?

EXEC GetCustomersByCity @City = 'London';