Stored Procedure Trong SQL: Viết Thủ Tục Lưu Trữ Và Tự Động Hoá Truy Vấn

1. Stored Procedure Là Gì? #

Stored Procedure (SP) là tập hợp câu lệnh SQL được lưu trữ trong database, có thể gọi lại bằng tên. Giống function trong lập trình: nhận parameters, thực thi logic, trả về kết quả. SP giúp tối ưu performance, bảo mật, và tái sử dụng code.

Use cases phổ biến: tự động tính lương cuối tháng, generate report, data cleanup, batch insert, audit logging. Thay vì chạy 10 queries thủ công, gom vào 1 SP và chạy 1 lệnh.

2. Tạo Stored Procedure Cơ Bản #

-- SQL Server syntax
CREATE PROCEDURE sp_GetTopCustomers
    @TopN INT = 10,
    @MinRevenue DECIMAL(18,2) = 0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@TopN)
        c.name, c.email,
        SUM(o.amount) AS total_revenue,
        COUNT(o.id) AS order_count
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.name, c.email
    HAVING SUM(o.amount) >= @MinRevenue
    ORDER BY total_revenue DESC;
END;

-- Gọi SP:
EXEC sp_GetTopCustomers @TopN = 5, @MinRevenue = 1000000;

3. Parameters: INPUT Và OUTPUT #

INPUT parameters truyền giá trị vào SP. OUTPUT parameters trả giá trị ra ngoài. Có thể set default values. Dùng OUTPUT khi cần lấy 1 giá trị (ID mới tạo, row count) mà không cần SELECT.

CREATE PROCEDURE sp_CreateOrder
    @CustomerID INT,
    @Amount DECIMAL(18,2),
    @NewOrderID INT OUTPUT  -- OUTPUT parameter
AS
BEGIN
    INSERT INTO orders (customer_id, amount, order_date)
    VALUES (@CustomerID, @Amount, GETDATE());
    
    SET @NewOrderID = SCOPE_IDENTITY();  -- Lấy ID vừa tạo
END;

-- Gọi với OUTPUT:
DECLARE @id INT;
EXEC sp_CreateOrder 42, 500000, @id OUTPUT;
PRINT @id;  -- In ra OrderID mới

4. Error Handling: TRY...CATCH #

SP production PHẢI có error handling. TRY...CATCH bắt lỗi runtime. Kết hợp TRANSACTION để rollback khi lỗi — đảm bảo data consistency (all or nothing).

CREATE PROCEDURE sp_TransferMoney
    @FromAccount INT, @ToAccount INT, @Amount DECIMAL
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
            UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;  -- Re-throw error
    END CATCH
END;

5. Cursor Và Vòng Lặp #

Cursor duyệt từng dòng kết quả — cần khi logic phức tạp không thể viết bằng set-based operations. Tuy nhiên, cursor RẤT CHẬM so với set-based SQL. Chỉ dùng khi thật sự cần thiết (VD: gọi SP khác cho mỗi dòng, dynamic SQL per row).

6. Dynamic SQL Trong SP #

sp_executesql thực thi SQL string được build runtime. Hữu ích khi tên bảng/cột là parameter. LUÔN dùng parameterized query để tránh SQL Injection — KHÔNG BAO GIỜ concatenate user input trực tiếp vào SQL string.

7. Best Practices #

1) Prefix sp_ cho system SP (SQL Server) — dùng usp_ hoặc prefix riêng. 2) SET NOCOUNT ON ở đầu SP. 3) Luôn có TRY...CATCH + TRANSACTION. 4) Comment parameters rõ ràng. 5) Tránh SELECT * — liệt kê cột cụ thể. 6) Version control SP scripts.

8. Kết Luận #

Stored Procedure là skill SQL Server/PostgreSQL quan trọng. Nắm: CREATE PROCEDURE + parameters, TRY...CATCH + TRANSACTION, OUTPUT parameters, và khi nào dùng cursor vs set-based. SP giúp code SQL tái sử dụng, bảo mật, và maintain dễ dàng hơn.