Stored Procedures trong SQL

Vấn đề: SQL Lặp đi Lặp lại trong Ứng dụng

Một ứng dụng xử lý đơn hàng: kiểm tra tồn kho, trừ số lượng, tạo đơn hàng, ghi log — cùng 120 dòng SQL chạy ở mọi API endpoint, batch job và mobile app. Thay đổi phải áp dụng ở mọi nơi, không nhất quán.

Stored procedures giải quyết bằng cách lưu logic SQL bên trong database. Ứng dụng chỉ gọi một tên, database thực thi logic đã lưu sẵn.

Stored Procedure là gì?

Stored procedure là một khối mã SQL được đặt tên, lưu trữ trong database, có thể thực thi theo yêu cầu. Nó nhận tham số đầu vào/đầu ra và chứa logic điều khiển luồng (Control Flow). Đặc điểm chính:

Được biên dịch trước (Pre-compiled) — kế hoạch thực thi (Execution Plan) được cache và tái sử dụng
Có tham số (Parameterized) — nhận tham số đầu vào và đầu ra
Đóng gói (Encapsulated) — ẩn chi tiết triển khai khỏi phía gọi
Giao dịch (Transactional) — có thể bao nhiều câu lệnh trong một giao dịch (Transaction) duy nhất

Cú pháp Cơ bản

Ví dụ sử dụng cú pháp SQL Server. Khác biệt chính của PostgreSQL/MySQL được ghi chú.

Tạo stored procedure

-- SQL Server
CREATE PROCEDURE GetEmployeesByDepartment
    @DeptId INT
AS
BEGIN
    SELECT id, name, salary
    FROM employees
    WHERE dept_id = @DeptId
    ORDER BY name;
END;

-- PostgreSQL (sử dụng FUNCTION, không phải PROCEDURE, để trả về giá trị)
CREATE OR REPLACE FUNCTION GetEmployeesByDepartment(dept_id INT)
RETURNS TABLE(id INT, name VARCHAR, salary DECIMAL)
LANGUAGE sql AS $$
    SELECT id, name, salary FROM employees
    WHERE dept_id = $1 ORDER BY name;
$$;

-- MySQL
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT id, name, salary FROM employees
    WHERE dept_id = dept_id ORDER BY name;
END //
DELIMITER ;

Thực thi, sửa đổi và xóa

-- Thực thi
EXEC GetEmployeesByDepartment @DeptId = 10;    -- SQL Server
SELECT * FROM GetEmployeesByDepartment(10);     -- PostgreSQL
CALL GetEmployeesByDepartment(10);              -- MySQL

-- Sửa đổi
ALTER PROCEDURE GetEmployeesByDepartment
    @DeptId INT
AS
BEGIN
    SELECT id, name, salary, hire_date
    FROM employees WHERE dept_id = @DeptId
    ORDER BY hire_date DESC;
END;

-- Xóa
DROP PROCEDURE GetEmployeesByDepartment;   -- SQL Server / MySQL
DROP FUNCTION GetEmployeesByDepartment;     -- PostgreSQL

Tham số (Parameters)

Tham số đầu vào với bộ lọc tùy chọn

CREATE PROCEDURE SearchEmployees
    @NameFilter NVARCHAR(100) = NULL,
    @MinSalary DECIMAL(18,2) = 0,
    @DeptId INT = NULL
AS
BEGIN
    SELECT id, name, salary, dept_id
    FROM employees
    WHERE (@NameFilter IS NULL OR name LIKE '%' + @NameFilter + '%')
      AND salary >= @MinSalary
      AND (@DeptId IS NULL OR dept_id = @DeptId)
    ORDER BY salary DESC;
END;

EXEC SearchEmployees @NameFilter = 'Alice', @MinSalary = 50000, @DeptId = 10;
EXEC SearchEmployees @DeptId = 10;     -- mặc định: NameFilter=NULL, MinSalary=0
EXEC SearchEmployees;                  -- trả về tất cả nhân viên

mẹo

Sử dụng giá trị mặc định NULL cho tham số tùy chọn. Mẫu WHERE (@Param IS NULL OR column = @Param) giúp bộ lọc trở nên tùy chọn — khi truyền NULL, điều kiện đó bị bỏ qua.

Tham số đầu ra (Output Parameters)

CREATE PROCEDURE GetDepartmentStats
    @DeptId INT,
    @EmployeeCount INT OUTPUT,
    @AvgSalary DECIMAL(18,2) OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*),
           @AvgSalary = AVG(salary)
    FROM employees WHERE dept_id = @DeptId;
END;

DECLARE @Count INT, @Avg DECIMAL(18,2);

EXEC GetDepartmentStats
    @DeptId = 10,
    @EmployeeCount = @Count OUTPUT,
    @AvgSalary = @Avg OUTPUT;

SELECT @Count AS employee_count, @Avg AS avg_salary;

Tham số giá trị bảng (Table-Valued Parameters) (SQL Server)

Truyền nhiều dòng dưới dạng một tham số duy nhất cho thao tác hàng loạt:

-- 1. Tạo kiểu bảng (Table Type)
CREATE TYPE EmployeeTableType AS TABLE (
    name NVARCHAR(100),
    dept_id INT,
    salary DECIMAL(18,2)
);

-- 2. Sử dụng làm tham số
CREATE PROCEDURE BulkInsertEmployees
    @Employees EmployeeTableType READONLY
AS
BEGIN
    INSERT INTO employees (name, dept_id, salary)
    SELECT name, dept_id, salary FROM @Employees;
END;

-- 3. Gọi
DECLARE @NewEmployees AS EmployeeTableType;
INSERT INTO @NewEmployees VALUES
    ('Alice', 10, 75000), ('Bob', 20, 65000), ('Carol', 30, 80000);

EXEC BulkInsertEmployees @Employees = @NewEmployees;

Luồng Điều khiển (Control Flow)

Stored procedures hỗ trợ logic thủ tục bên trong database.

Biến và Điều kiện

CREATE PROCEDURE ApplyRaise
    @EmployeeId INT,
    @RaisePercent DECIMAL(5,2)
AS
BEGIN
    DECLARE @CurrentSalary DECIMAL(18,2);
    DECLARE @NewSalary DECIMAL(18,2);
    DECLARE @MaxSalary DECIMAL(18,2) = 200000;

    SELECT @CurrentSalary = salary FROM employees WHERE id = @EmployeeId;
    SET @NewSalary = @CurrentSalary * (1 + @RaisePercent / 100);

    IF @NewSalary > @MaxSalary
    BEGIN
        SET @NewSalary = @MaxSalary;
        PRINT 'Salary capped at maximum';
    END

    UPDATE employees SET salary = @NewSalary WHERE id = @EmployeeId;
    SELECT @CurrentSalary AS old_salary, @NewSalary AS new_salary;
END;

Vòng lặp

CREATE PROCEDURE GenerateMonthlyReport @Year INT
AS
BEGIN
    DECLARE @Month INT = 1;
    CREATE TABLE #MonthlyStats (
        month INT, order_count INT, total_revenue DECIMAL(18,2)
    );

    WHILE @Month <= 12
    BEGIN
        INSERT INTO #MonthlyStats (month, order_count, total_revenue)
        SELECT @Month, COUNT(*), ISNULL(SUM(amount), 0)
        FROM orders
        WHERE YEAR(order_date) = @Year AND MONTH(order_date) = @Month;

        SET @Month = @Month + 1;
    END

    SELECT * FROM #MonthlyStats ORDER BY month;
    DROP TABLE #MonthlyStats;
END;

Xử lý Lỗi (Error Handling)

TRY...CATCH (SQL Server)

CREATE PROCEDURE TransferFunds
    @FromAccountId INT,
    @ToAccountId INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF @Amount <= 0
            RAISERROR('Transfer amount must be positive', 16, 1);

        DECLARE @Balance DECIMAL(18,2);
        SELECT @Balance = balance FROM accounts WHERE id = @FromAccountId;

        IF @Balance < @Amount
            RAISERROR('Insufficient funds: balance is %s', 16, 1, CAST(@Balance AS VARCHAR(20)));

        UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccountId;
        UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccountId;

        INSERT INTO transfer_log (from_account, to_account, amount, transfer_date)
        VALUES (@FromAccountId, @ToAccountId, @Amount, GETDATE());

        COMMIT TRANSACTION;
        SELECT 'Transfer successful' AS result;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SELECT
            ERROR_NUMBER()    AS error_number,
            ERROR_MESSAGE()   AS error_message,
            ERROR_SEVERITY()  AS error_severity,
            ERROR_PROCEDURE() AS error_procedure,
            ERROR_LINE()      AS error_line;
    END CATCH
END;

Xử lý EXCEPTION (PostgreSQL)

CREATE OR REPLACE FUNCTION TransferFunds(
    from_account_id INT, to_account_id INT, amount DECIMAL
)
RETURNS TEXT LANGUAGE plpgsql AS $$
DECLARE balance DECIMAL;
BEGIN
    IF amount <= 0 THEN
        RAISE EXCEPTION 'Transfer amount must be positive';
    END IF;

    SELECT balance INTO balance FROM accounts WHERE id = from_account_id;
    IF balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: balance is %', balance;
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;

    INSERT INTO transfer_log (from_account, to_account, amount, transfer_date)
    VALUES (from_account_id, to_account_id, amount, NOW());

    RETURN 'Transfer successful';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Transfer failed: ' || SQLERRM;
END;
$$;

Stored Procedures so với Hàm do Người dùng Định nghĩa (User-Defined Functions)

Cả hai đều là database object được đặt tên, nhưng hành vi rất khác nhau:

	Stored Procedure	Scalar Function (Hàm vô hướng)	Table-Valued Function (Hàm trả về bảng)
Giá trị trả về	Tùy chọn (0+ result set)	Một giá trị vô hướng duy nhất	Một bảng
Sử dụng	Chỉ `EXEC` / `CALL`	Trong `SELECT`, `WHERE`	Trong mệnh đề `FROM`
Sửa đổi dữ liệu	Có (INSERT, UPDATE, DELETE)	Không — chỉ đọc	Không — chỉ đọc
Giao dịch	Có thể BEGIN/COMMIT/ROLLBACK	Không thể	Không thể
Xử lý lỗi	TRY/CATCH đầy đủ	Hạn chế	Hạn chế
Side effect	Được phép	Không được phép	Không được phép

cảnh báo

Scalar functions trong SELECT hoặc WHERE thực thi một lần cho mỗi dòng. Trên bảng 1M dòng, hàm được gọi 1M lần — nguyên nhân giảm hiệu năng nghiêm trọng. Sử dụng inline table-valued functions hoặc SQL thuần thay thế.

Cân nhắc Hiệu năng (Performance Considerations)

Cache Kế hoạch Thực thi (Plan Caching)

Lần thực thi đầu tiên, query optimizer tạo kế hoạch thực thi (Execution Plan) và cache. Các lần sau tái sử dụng plan đã cache — không cần phân tích hay biên dịch lại.

Vấn đề "Parameter Sniffing" (Ngửi tham số)

Optimizer tạo plan dựa trên giá trị tham số đầu tiên mà nó thấy. Nếu lần gọi đầu dùng bộ lọc chọn lọc cao (5 dòng), plan tối ưu cho kết quả nhỏ. Lần sau với bộ lọc ít chọn lọc (500K dòng) hoạt động kém với plan đã cache.

-- Biên dịch lại mỗi lần thực thi (SQL Server)
CREATE PROCEDURE SearchOrders @Status VARCHAR(20)
WITH RECOMPILE AS ...

-- Biên dịch lại chỉ câu lệnh cụ thể
SELECT * FROM orders WHERE status = @Status
OPTION (RECOMPILE);

-- Sử dụng thống kê trung bình thay vì sniffing
CREATE PROCEDURE SearchOrders @Status VARCHAR(20)
WITH OPTIMIZE FOR UNKNOWN AS ...

Lưu lượng mạng (Network Traffic)

Cách tiếp cận	Được gửi qua mạng
Ad-hoc SQL	Toàn bộ văn bản SQL (hàng trăm dòng)
Stored procedure	Chỉ `EXEC ProcessOrder @id = 1001`

Bảo mật: Kiểm soát Quyền (Permission Control)

Stored procedures cho phép kiểm soát truy cập chi tiết mà không cần phơi bày bảng bên dưới. Tuân theo nguyên tắc đặc quyền tối thiểu (Principle of Least Privilege).

-- Ứng dụng chỉ cần quyền EXECUTE — không cần truy cập trực tiếp bảng
GRANT EXECUTE ON ProcessOrder TO web_app_role;

SQL Động trong Stored Procedures (Dynamic SQL)

Xây dựng câu lệnh SQL động tại runtime — ORDER BY động, WHERE động, hoặc tên bảng động.

-- SQL Server: sp_executesql với tham số
CREATE PROCEDURE SearchEmployeesDynamic
    @NameFilter NVARCHAR(100) = NULL,
    @SortColumn NVARCHAR(50) = 'name',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @Params NVARCHAR(MAX) = N'@NameFilter NVARCHAR(100)';

    SET @SQL = N'
        SELECT id, name, salary, hire_date FROM employees
        WHERE (@NameFilter IS NULL OR name LIKE ''%'' + @NameFilter + ''%'')
        ORDER BY ' +
        CASE @SortColumn
            WHEN 'name' THEN 'name'
            WHEN 'salary' THEN 'salary'
            WHEN 'hire_date' THEN 'hire_date'
            ELSE 'name'
        END + ' ' +
        CASE @SortDirection WHEN 'DESC' THEN 'DESC' ELSE 'ASC' END;

    EXEC sp_executesql @SQL, @Params, @NameFilter = @NameFilter;
END;

-- PostgreSQL: EXECUTE ... USING với format()
CREATE OR REPLACE FUNCTION SearchEmployeesDynamic(
    name_filter TEXT DEFAULT NULL,
    sort_column TEXT DEFAULT 'name',
    sort_direction TEXT DEFAULT 'ASC'
)
RETURNS TABLE(id INT, name VARCHAR, salary DECIMAL, hire_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
    IF sort_column NOT IN ('name', 'salary', 'hire_date') THEN
        sort_column := 'name';
    END IF;
    IF sort_direction NOT IN ('ASC', 'DESC') THEN
        sort_direction := 'ASC';
    END IF;

    RETURN QUERY EXECUTE format(
        'SELECT id, name, salary, hire_date FROM employees
         WHERE ($1::text IS NULL OR name LIKE ''%'' || $1 || ''%'')
         ORDER BY %I %s',
        sort_column, sort_direction
    ) USING name_filter;
END;
$$;

cảnh báo

Không bao giờ nối input người dùng trực tiếp vào dynamic SQL — tạo ra lỗ hổng SQL Injection. Luôn sử dụng truy vấn có tham số (sp_executesql / EXECUTE ... USING).

Stored Procedures so với Mã Ứng dụng (Application Code)

	Stored Procedures	Mã Ứng dụng
Hiệu năng	Gần dữ liệu hơn, ít round-trip	Overhead mạng cho mỗi truy vấn
Khả năng test	Khó unit test	Dễ unit test
Quản lý phiên bản	Cần migration script	Quy trình Git tự nhiên
Debug	Công cụ hạn chế	Debug đầy đủ trong IDE
Khả năng chuyển đổi	Cú pháp đặc thù database	Database-agnostic (với ORM)
Khả năng mở rộng	Mở rộng theo database server	Mở rộng độc lập

mẹo

Ứng dụng hiện đại sử dụng cách tiếp cận kết hợp: mã ứng dụng xử lý logic nghiệp vụ, stored procedures xử lý thao tác nặng về dữ liệu và đóng gói bảo mật.

Các Lỗi Phổ biến (Common Pitfalls)

Lạm dụng stored procedures — đặt tất cả logic nghiệp vụ trong database khiến khó test, quản lý phiên bản và mở rộng. Chỉ dùng cho thao tác tập trung vào dữ liệu.
Bỏ qua parameter sniffing — plan đã cache tối ưu cho một giá trị tham số có thể hoạt động kém cho giá trị khác. Theo dõi và sử dụng RECOMPILE khi cần.
Không xử lý lỗi — lỗi không xử lý để lại giao dịch mở và gây blocking. Luôn sử dụng TRY...CATCH.
Scalar functions trong WHERE — thực thi per-row, phá hủy hiệu năng trên bảng lớn. Viết lại thành inline TVFs hoặc SQL thuần.
Dynamic SQL injection — nối input người dùng vào chuỗi SQL là lỗ hổng nghiêm trọng. Luôn sử dụng dynamic SQL có tham số.
Thiếu SET NOCOUNT ON — SQL Server gửi thông báo DONE_IN_PROC sau mỗi câu lệnh, tăng lưu lượng mạng không cần thiết.

Best Practices

Giữ procedure tập trung — mỗi procedure làm tốt một việc. Tránh "god procedures."
Sử dụng SET NOCOUNT ON — ngăn thông báo row-count, giảm lưu lượng mạng.
Xử lý lỗi rõ ràng — bọc sửa đổi dữ liệu trong TRY...CATCH, luôn rollback giao dịch thất bại.
Thêm schema cho object — sử dụng dbo.GetEmployees, không chỉ GetEmployees, để tăng hiệu năng và rõ ràng.
Tham số hóa mọi thứ — không bao giờ hardcode giá trị. Sử dụng tham số với giá trị mặc định hợp lý.
Xác thực đầu vào — kiểm tra giá trị tham số ở đầu và raise lỗi rõ ràng cho đầu vào không hợp lệ.
Quản lý phiên bản DDL — lưu script CREATE PROCEDURE trong hệ thống migration.

Câu hỏi Phỏng vấn (Interview Questions)

1. Stored procedure là gì và những lợi ích của nó?

Một tập hợp các câu lệnh SQL được đặt tên, lưu trữ trong database, có thể thực thi theo yêu cầu.

Lợi ích	Mô tả
Hiệu năng (Performance)	Kế hoạch thực thi (Execution Plan) được cache và tái sử dụng, tránh biên dịch lại
Giảm lưu lượng mạng	Chỉ lời gọi procedure được gửi, không phải toàn bộ SQL
Bảo mật (Security)	Cấp quyền EXECUTE mà không cần truy cập trực tiếp bảng
Tái sử dụng (Reusability)	Viết một lần, gọi từ bất kỳ ứng dụng nào
Đóng gói (Encapsulation)	Logic database tập trung và ẩn khỏi phía gọi

2. Stored procedure khác function ở đâu?

	Stored Procedure	Function
Giá trị trả về	Tùy chọn (0+ result set)	Phải trả về giá trị hoặc bảng
Sử dụng	`EXEC` / `CALL`	Bên trong `SELECT`, `WHERE`, `FROM`
Sửa đổi dữ liệu	Có thể INSERT, UPDATE, DELETE	Chỉ đọc
Giao dịch	Có thể BEGIN/COMMIT/ROLLBACK	Không thể
Xử lý lỗi	TRY/CATCH đầy đủ	Hạn chế

3. Parameter sniffing là gì và cách xử lý?

Optimizer tạo kế hoạch thực thi dựa trên giá trị tham số đầu tiên mà nó thấy. Plan đã cache có thể không tối ưu cho giá trị khác.

Ví dụ: Lần gọi đầu dùng status = 'shipped' (99% dòng) → quét toàn bảng. Lần hai dùng status = 'cancelled' (0.1% dòng) → plan quét toàn bảng đã cache kém hiệu quả.

Giải pháp: WITH RECOMPILE, OPTION (RECOMPILE), OPTIMIZE FOR UNKNOWN, hoặc copy tham số vào biến cục bộ.

4. Cách xử lý lỗi trong stored procedures?

BEGIN TRY
    BEGIN TRANSACTION;
    -- Các câu lệnh SQL
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH

Nguyên tắc: bọc sửa đổi trong TRY...CATCH, kiểm tra @@TRANCOUNT trước rollback, trả về thông tin lỗi có ý nghĩa, ghi log lỗi vào bảng lỗi.

5. Dynamic SQL là gì và rủi ro của nó?

SQL được xây dựng dưới dạng chuỗi tại runtime. Hữu ích cho ORDER BY/WHERE động, tên bảng/cột động, truy vấn liên database.

Rủi ro: SQL Injection (nối input người dùng), khó debug, ô nhiễm plan cache (Plan Cache Pollution).

Phòng ngừa: Sử dụng sp_executesql với tham số (SQL Server) hoặc EXECUTE ... USING (PostgreSQL), whitelist tên cột/bảng, dùng quotename() / format() cho identifier.

6. Khi nào dùng stored procedure so với mã ứng dụng?

Stored procedures cho: thao tác nặng về dữ liệu, đóng gói bảo mật, nhất quán giữa các ứng dụng, luồng quan trọng về hiệu năng.

Mã ứng dụng cho: quy tắc nghiệp vụ phức tạp cần unit test, khả năng chuyển đổi database, gọi service bên ngoài, yêu cầu thay đổi nhanh.

Tìm hiểu Thêm

ACID Properties — các đảm bảo giao dịch bảo vệ thao tác stored procedure
Database Locking — cách locking ảnh hưởng đến thực thi stored procedure đồng thời
SQL Joins — joins thường được sử dụng bên trong stored procedures

Vấn đề: SQL Lặp đi Lặp lại trong Ứng dụng​

Stored Procedure là gì?​

Cú pháp Cơ bản​

Tạo stored procedure​

Thực thi, sửa đổi và xóa​

Tham số (Parameters)​

Tham số đầu vào với bộ lọc tùy chọn​

Tham số đầu ra (Output Parameters)​

Tham số giá trị bảng (Table-Valued Parameters) (SQL Server)​

Luồng Điều khiển (Control Flow)​

Biến và Điều kiện​

Vòng lặp​

Xử lý Lỗi (Error Handling)​

TRY...CATCH (SQL Server)​

Xử lý EXCEPTION (PostgreSQL)​

Stored Procedures so với Hàm do Người dùng Định nghĩa (User-Defined Functions)​

Cân nhắc Hiệu năng (Performance Considerations)​

Cache Kế hoạch Thực thi (Plan Caching)​

Vấn đề "Parameter Sniffing" (Ngửi tham số)​

Lưu lượng mạng (Network Traffic)​

Bảo mật: Kiểm soát Quyền (Permission Control)​

SQL Động trong Stored Procedures (Dynamic SQL)​

Stored Procedures so với Mã Ứng dụng (Application Code)​

Các Lỗi Phổ biến (Common Pitfalls)​

Best Practices​

Câu hỏi Phỏng vấn (Interview Questions)​

1. Stored procedure là gì và những lợi ích của nó?​

2. Stored procedure khác function ở đâu?​

3. Parameter sniffing là gì và cách xử lý?​

4. Cách xử lý lỗi trong stored procedures?​

5. Dynamic SQL là gì và rủi ro của nó?​

6. Khi nào dùng stored procedure so với mã ứng dụng?​

Tìm hiểu Thêm​