Hướng dẫn tạo sử dụng 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 (DBMS) hỗ trợ, tuy nhiên không phải tất cả đều hỗ trợ Stored Procedure.

Những lợi ích khi sử dụng Stored procedure

Một stored procedure cung cấp một lớp bảo mật quan trọng giữa user và database. Những người dùng cuối không thể thực thi được một số tác vụ quan trọng trong database, bằng cách cung cấp các stored procedure, người dùng cuối có thể thoải moái sử dụng để thực thi các tác vụ này mà không cần quan tâm bên dưới đang làm gì. 

Stored Procedure được biên dịch và lưu vào bộ nhớ khi khởi tạo. Điều đó có nghĩa rằng nó sẽ thực thi nhanh hơn so với việc gửi từng đoạn lệnh SQL tới SQL Server. Vì nếu bạn gửi từng đoạn lệnh nhiều lần thì SQL Server cũng sẽ phải biên dịch lại nhiều lần, rất mất thời gian so với việc biên dịch sẵn.

Chúng ta cũng có thể sử dụng lại các stored procedure trong ứng dụng, khi mà chúng ta chỉ cần viết một lần, và các mã lệnh có thể gọi đến nó bất kỳ lúc nào.

Stored 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

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

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

Create procedure <procedure_Name> 
As 
Begin 
<SQL Statement> 
END;

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.

CREATE PROCEDURE SelectCustomerstabledata 
AS 
BEGIN
     SELECT * 
     FROM Testdb.Customers 
END;

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

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
3   kaushik    23        Kota              2000.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 

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

CREATE PROCEDURE SelectCustomerstabledata 
AS 
BEGIN
     SELECT * 
     FROM Testdb.Customers 
     WHERE AGE >= 25
END;

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

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 

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

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 

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

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 

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:

Nguồn tham khảo

Công cụ tạo kí tự đặc biệt đẳng cấp tại: Kitudacbiet.co

https://searchoracle.techtarget.com/definition/stored-procedure

https://www.tutorialspoint.com/t_sql/t_sql_stored_procedures.htm

SQL Server stored procedures for beginners

https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver15