Hướng Dẫn Tạo Sử Dụng Stored Procedure Trong SQL Server

Mục lục

• 1 Những lợi ích khi sử dụng Stored procedure
• 2 Stored procedure trong SQL Server
  • 2.1 Tạo Stored procedure
  • 2.2 Thực thi store procedure trong sql server
  • 2.3 Cập nhật stored procedure trong sql server
  • 2.4 Xoá stored procudure
  • 2.5 Tạo stored procedure với tham số đầu vào
  • 2.6 Tạo stored procedure với tham số đầu vào có giá trị mặc định
  • 2.7 Tạo stored procedure với tham số output
  • 2.8 Thực thi store procedure với tham số output

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:

Total

4

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