Stored procedure thủ tục lưu trữ trong SQL Server

Stored procedure trong SQL Server

Stored Procedure là một tập hợp các câu lệnh SQL dùng để thực thi một nhiệm vụ nhất định. Nó có thể xem như một hàm trong các ngôn ngữ lập trình.

Stored procedure là một khái niệm khá phổ biến và được hầu hết các hệ quản trị cơ sở dữ liệu (RDBMS – Relational Database Management System) hỗ trợ, tuy nhiên không phải tất cả đều hỗ trợ Stored Procedure.

Lợi ích khi dùng Procedure trong SQL

• Dễ dàng chỉnh sửa: Lập trình viên dễ dàng chỉnh sửa code bên trong thủ tục ở SQL Server mà không cần khởi động lại hay triển khai ứng dụng. Ví dụ, nếu truy vấn T-SQL được viết trong ứng dụng và bạn cần thay đổi logic, bạn phải thay code trong ứng dụng đó và triển khai lại nó. Thủ tục trong SQL Server loại bỏ những khó khăn đó bằng cách lưu trữ code trong database. Vì thế, khi muốn thay đổi logic bên trong procedure, bạn chỉ có thể làm việc đó bằng lệnh Alter Procedure đơn giản.
• Giảm tải truy cập mạng: Khi dùng procedure được lưu trữ thay vì viết các truy vấn T-SQL ở cấp ứng dụng, chỉ tên thủ tục được chuyển qua mạng thay vì toàn bộ code T-SQL.
• Có thể tái sử dụng: Bạn có thể triển khai procedure được lưu trữ bằng nhiều người dùng hoặc nhiều ứng dụng client mà không cần viết lại code.
• Bảo mật: Giảm các mối đe dọa bằng cách xóa truy cập trực tiếp tới bảng.

Bài viết sẽ cung cấp cho bạn cú pháp và những ví dụ về cách tạo và xóa bỏ procedure trong SQL Server. Để thao tác với stored procedure trong SQL Server, trước tiên mình sẽ tạo một database gồm 1 bảng CUSTOMERS như sau:

Tạo Stored procedure

Để tạo một stored procedure trong SQL Server chúng ta có thể theo dõi cú pháp sau

Ví dụ tạo một stored procedure lấy danh sách tất cả dòng dữ liệu trong bảng CUSTOMERS.

Thực thi store procedure trong sql server

Sau khi khởi tạo thành công store procedure, chúng ta có thể thực thi chúng thông qua cú pháp

EXEC ProcedureName

Ví dụ thực thi SelectCustomerstabledata store procedure

EXEC SelectCustomerstabledata;

Output

Cập nhật stored procedure trong sql server

Để thay đổi stored thì bạn sử dụng lệnh ALTER PROCEDURE cộng với tên của stored procedure.

Ví dụ như mình thêm điều kiện vào SelectCustomerstabledata, lọc ra những CUSTOMERS có AGE >= 25

Khi thực thi sẽ có kết quả sau

Xoá stored procudure

Để xóa stored procedure chúng ta có thể sử dụng lệnh DROP PROCEDURE hoặc DROP PROC

DROP PROCEDURE sp_name;

# or

DROP PROC sp_name

Tạo stored procedure với tham số đầu vào

Để tạo store procedure tham số đầu vào trong sql server, chúng ta có thể xem tham số sau

Create procedure <procedure_Name> (@param_name1 param_type1, @param_name2 param_type2, …, @param_namen param_typen,)

As

Begin

<SQL Statement>

END;

Ví dụ mình sẽ chuyển store procudure vừa được cập nhật ở trên sang nhận một tham số age thay vì hard code như trên

CREATE PROCEDURE SelectCustomerstabledata (@AGE INT)

AS

BEGIN

    SELECT *

    FROM Testdb.Customers

    WHERE AGE >= @AGE

END;

Thực thi đoạn mã sau

EXEC SelectCustomerstabledata 25;

Output

Tạo stored procedure với tham số đầu vào có giá trị mặc định

Chúng ta cũng có thể khởi tạo giá trị mặc định cho các tham số đầu vào, khi chúng được thực thi mà không được truyền giá trị thì các giá trị mặc định này sẽ áp dụng, ngược lại các giá trị tham số đầu vào sẽ được áp dụng.

CREATE PROCEDURE SelectCustomerstabledata (@AGE INT = 25)

AS

BEGIN

    SELECT *

    FROM Testdb.Customers

    WHERE AGE >= @AGE

END;

Khi thực thi

EXEC SelectCustomerstabledata 25;

hay

EXEC SelectCustomerstabledata;

Thì kết quả sẽ hoàn toàn giống nhau

Tạo stored procedure với tham số output

Để tạo một tham số OUTPUT thì bạn vẫn sử dụng cách tạo như một tham số bình thường và chỉ cần thêm từ khoá OUTPUT vào cuối.

Mỗi stored procedure có thể có một hoặc nhiều tham số OUTPUT. Ví dụ:

CREATE PROCEDURE SelectCustomerstabledata (@AGE INT = 25, @CUSTOMER_COUNT INT OUTPUT)

AS

BEGIN

    SELECT *

    FROM Testdb.Customers

    WHERE AGE >= @AGE SELECT @CUSTOMER_COUNT = @@ROWCOUNT;

END;

Trong đó:

• @@ROWCOUNT dùng để lưu trữ tổng số dòng bị ảnh hưởng với câu truy vấn cuối cùng trong sql server.
• SELECT @CUSTOMER_COUNT = @@ROWCOUNT; gán tổng số dòng vào CUSTOMER_COUNT.

Thực thi store procedure với tham số output

Sau khi khởi tạo thành công stored procedure với tham số output, chúng ta có thể thực thi chúng như sau:

DECLARE @COUNT INT;

EXEC SelectCustomerstabledata

    @AGE = 25

    @CUSTOMER_COUNT = @COUNT;

SELECT @COUNT AS ‘TOTAL’;

Output:

TOTAL: 5

Qua bài viết, hy vọng mọi người được hiểu hơn về stored procedure trong SQL Server.

P.A Việt Nam cung cấp đa dạng các Plan Hosting đáp ứng yêu cầu của khách hàng
Hosting Phổ Thông
Hosting Chất Lượng Cao

Tham khảo các ưu đãi: https://www.pavietnam.vn/vn/tin-khuyen-mai/