Stored Procedure Trong SQL Server

Stored procedure trong sql là một phần kiến thức quan trọng, hỗ trợ rất nhiều cho lập trình và cấu hình cơ sở dữ liệu. Tuy nhiên, vẫn còn nhiều người chưa hiểu rõ về cách sử dụng, cú pháp, ý nghĩa của nó. Vì thế, trong bài viết này, Comdy sẽ hướng dẫn bạn chi tiết, tất tần tật về Stored Procedure trong sql server nhé.

Stored procedure trong SQL là gì?

Table of Contents

Toggle

• Stored procedure trong SQL là gì?
• Exec trong SQL là gì?
• Hướng dẫn cơ bản về cách tạo procedure trong SQL Server
  • Create procedure trong SQL server
  • Thực thi stored procedure SQL server
  • Sửa đổi stored proc SQL Server
  • Xóa procedure SQL server
• Stored procedure có tham số trong SQL Server
  • Tạo stored procedure có một tham số trong SQL Server
  • Thực thi stored procedure có một tham số trong SQL Server
  • Tạo stored procedure có nhiều tham số trong SQL Server
  • Sử dụng tên của tham số khi thực thi stored procedure trong SQL Server
  • Tham số kiểu chuỗi ký tự cho stored procedure trong SQL Server
  • Tạo các tham số tùy chọn cho stored procedure trong SQL Server
  • Sử dụng NULL làm giá trị mặc định cho tham số của stored procedure trong SQL Server
• Biến trong SQL Server
  • Biến là gì?
  • Khai báo biến
  • Gán giá trị cho biến
  • Sử dụng biến trong truy vấn
  • Lưu trữ kết quả truy vấn trong biến
  • Select một bản ghi vào biến
  • Cộng dồn các giá trị vào một biến
• Tham số OUTPUT của stored procedure trong SQL Server
  • Tạo tham số OUTPUT cho stored procedure
  • Thực thi stored procedure với các tham số OUTPUT
• Câu lệnh BEGIN … END trong SQL Server
  • Tổng quan về câu lệnh BEGIN…END trong SQL Server
  • Câu lệnh BEGIN … END lồng nhau trong SQL Server
• CURSOR trong SQL Server
  • CURSOR là gì?
  • Vòng đời của CURSOR trong SQL Server
  • Ví dụ CURSOR trong SQL Server
• @@rowcount là gì?
• Nguyên tắc viết viết stored procedure trong sql
• Lệnh print trong SQL server
• Set nocount on trong SQL server
• Kết lại

Stored procedure là gì? Stored procedure trong SQL Server hay còn gọi là thủ tục trong SQL server, được sử dụng để nhóm một hoặc nhiều câu lệnh Transact-SQL thành các đơn vị logic. Stored procedure được lưu trữ dưới dạng các đối tượng được đặt tên trong máy chủ cơ sở dữ liệu SQL Server.

Khi bạn gọi một stored procedure lần đầu tiên, SQL Server sẽ tạo một kế hoạch thực thi và lưu trữ nó trong bộ đệm. Trong các lần thực thi tiếp theo của stored procedure, SQL Server sử dụng lại kế hoạch để stored procedure có thể thực thi rất nhanh với hiệu suất đáng tin cậy.

Exec trong SQL là gì?

EXEC thường được sử dụng để xem stored procedure trong sql hoặc một hàm. Stored procedure trong sql server là một tập hợp các câu lệnh SQL được lưu trữ trong cơ sở dữ liệu để thực thi. Khi bạn sử dụng EXEC (viết tắt của “execute”) trong SQL, bạn đang gọi một stored procedure hoặc hàm cụ thể để thực hiện các công việc cụ thể.

Hướng dẫn cơ bản về cách tạo procedure trong SQL Server

Hướng dẫn cơ bản về cách tạo procedure trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách quản lý các stored procedure trong SQL Server bao gồm tạo, thực thi, sửa đổi và xóa các proc sql.

Create procedure trong SQL server

Câu lệnh SELECT sau trả về danh sách các sản phẩm từ bảng products trong cơ sở dữ liệu mẫu BikeStores:

SELECT product_name, list_price FROM production.products ORDER BY product_name;

Để tạo stored procedure bao bọc truy vấn này, bạn sử dụng câu lệnh CREATE PROCEDURE như sau:

CREATE PROCEDURE uspProductList AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY product_name; END;

Trong cú pháp này:

• uspProductList là tên của stored procedure.
• Từ khóa AS ngăn cách tiêu đề và phần thân của stored procedure.
• Nếu stored procedure có một câu lệnh, các từ khóa BEGIN và END bao quanh câu lệnh là tùy chọn. Tuy nhiên, đó là một thực hành tốt làm cho mã rõ ràng hơn.

Lưu ý: ngoài từ khóa CREATE PROCEDURE, bạn có thể sử dụng từ khóa CREATE PROC để làm cho câu lệnh ngắn hơn.

Để biên dịch procedure sql server này, bạn thực thi nó như một câu lệnh SQL bình thường trong SQL Server Management Studio như trong hình sau:

Nếu mọi thứ đều chính xác, thì bạn sẽ thấy thông báo sau:

Commands completed successfully.

Nó có nghĩa là proc trong sql đã được biên dịch và lưu trữ thành công vào cơ sở dữ liệu.

Bạn có thể tìm thấy stored procedure trong Object Explorer, trong Programmability > Stored Procedures như trong hình sau:

Đôi khi, bạn cần nhấp vào nút Refresh để cập nhật thủ công các đối tượng cơ sở dữ liệu trong Object Explorer.

Thực thi stored procedure SQL server

Để thực thi stored procedure sql, bạn sử dụng câu lệnh EXECUTE hoặc EXEC theo sau là tên của stored procedure như sau:

EXECUTE sp_name;

Hoặc là

EXEC sp_name;

Với sp_name là tên của stored procedure mà bạn muốn thực thi.

Ví dụ, để thực thi stored procedure uspProductList, bạn sử dụng câu lệnh sau:

EXEC uspProductList;

Stored procedure trả về đầu ra sau:

Sửa đổi stored proc SQL Server

Để sửa đổi stored procedure trong SQL Server, bạn sử dụng câu lệnh ALTER PROCEDURE.

Đầu tiên, stored procedure để xem nội dung của nó bằng cách bấm chuột phải vào tên stored procedure và chọn menu Modify:

Thứ hai, thay đổi nội dung của stored procedure mà trong ví dụ này là sắp xếp các sản phẩm theo giá niêm yết thay vì tên sản phẩm:

ALTER PROCEDURE uspProductList AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY list_price END;

Thứ ba, nhấp vào nút Execute, SQL Server sửa đổi stored procedure và trả về thông báo sau:

Commands completed successfully.

Bây giờ, nếu bạn thực thi lại stored procedure, bạn sẽ thấy các thay đổi đã có hiệu lực:

EXEC uspProductList;

Sau đây cho thấy một phần kết quả đầu ra:

Xóa procedure SQL server

Xóa thủ tục trong sql như thế nào? Để xóa stored procedure trong SQL Server, bạn sử dụng câu lệnh DROP PROCEDURE hoặc DROP PROC như sau:

DROP PROCEDURE sp_name;

Hoặc là

DROP PROC sp_name;

Với sp_name là tên của stored procedure mà bạn muốn xóa.

Ví dụ: để xóa stored procedure uspProductList, bạn thực thi câu lệnh sau:

DROP PROCEDURE uspProductList;

Trong phần này, bạn đã học cách quản lý các stored procedure trong SQL Server bao gồm tạo, thực thi, sửa đổi và xóa các stored procedure.

Stored procedure có tham số trong SQL Server

Stored procedure cho phép truyền một hoặc nhiều tham số cho nó

Trong phần này, chúng tôi sẽ mở rộng stored procedure cho phép truyền một hoặc nhiều tham số cho nó. Kết quả của stored procedure sẽ thay đổi dựa trên giá trị của các tham số.

Tạo stored procedure có một tham số trong SQL Server

Truy vấn sau đây trả về danh sách sản phẩm từ bảng products trong cơ sở dữ liệu mẫu BikeStores:

SELECT product_name, list_price FROM production.products ORDER BY list_price;

Bạn có thể tạo stored procedure bao bọc truy vấn này bằng cách sử dụng câu lệnh CREATE PROCEDURE như sau:

CREATE PROCEDURE uspFindProducts AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY list_price; END;

Tuy nhiên, lần này chúng ta có thể thêm một tham số vào stored procedure để tìm các sản phẩm có giá niêm yết lớn hơn giá đầu vào:

ALTER PROCEDURE uspFindProducts ( @min_list_price AS DECIMAL ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price ORDER BY list_price; END;

Trong ví dụ này:

• Đầu tiên, chúng tôi đã thêm một tham số có tên là @min_list_price vào stored procedure uspFindProducts. Mỗi tham số phải bắt đầu bằng ký hiệu @. Từ khóa AS DECIMAL chỉ định kiểu dữ liệu của tham số @min_list_price. Tham số phải được bao quanh bởi cặp dấu ngoặc tròn.
• Thứ hai, chúng tôi đã sử dụng tham số @min_list_price trong mệnh đề WHERE của câu lệnh SELECT để lọc các sản phẩm có giá niêm yết lớn hơn hoặc bằng @min_list_price.

Thực thi stored procedure có một tham số trong SQL Server

Để thực thi stored procedure uspFindProducts, bạn truyền một đối số cho nó như sau:

EXEC uspFindProducts 100;

Sau đây cho thấy đầu ra:

Stored procedure trả về tất cả các sản phẩm có giá niêm yết lớn hơn hoặc bằng 100.

Nếu bạn thay đổi đối số thành 200, bạn sẽ nhận được một tập kết quả khác:

EXEC uspFindProducts 200;

Sau đây cho thấy đầu ra:

Tạo stored procedure có nhiều tham số trong SQL Server

Stored procedure có thể có một hoặc nhiều tham số. Các tham số được phân tách bằng dấu phẩy.

Câu lệnh sau sửa đổi stored procedure uspFindProducts bằng cách thêm một tham số được có tên @max_list_price cho nó:

ALTER PROCEDURE uspFindProducts ( @min_list_price AS DECIMAL, @max_list_price AS DECIMAL ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND list_price <= @max_list_price ORDER BY list_price; END;

Khi stored procedure được sửa đổi thành công, bạn có thể thực thi nó bằng cách truyền hai đối số vào stored procedure, một cho @min_list_price và một cho @max_list_price như sau:

EXEC uspFindProducts 900, 1000;

Sau đây cho thấy đầu ra:

Sử dụng tên của tham số khi thực thi stored procedure trong SQL Server

Trong trường hợp các stored procedure có nhiều tham số, sẽ tốt hơn và rõ ràng hơn khi thực thi cácstored procedure bằng cách sử dụng tên của các tham số.

Ví dụ, câu lệnh sau thực thi stored procedure uspFindProducts bằng cách sử dụng tên của các tham số @min_list_price và @max_list_price:

EXEC uspFindProducts @min_list_price = 900, @max_list_price = 1000;

Kết quả của stored procedure là như nhau tuy nhiên câu lệnh sẽ rõ ràng hơn.

Tham số kiểu chuỗi ký tự cho stored procedure trong SQL Server

Câu lệnh sau đây thêm tham số @name kiểu chuỗi ký tự vào stored procedure.

ALTER PROCEDURE uspFindProducts ( @min_list_price AS DECIMAL, @max_list_price AS DECIMAL, @name AS VARCHAR(max) ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND list_price <= @max_list_price AND product_name LIKE '%' + @name + '%' ORDER BY list_price; END;

Trong mệnh đề WHERE của câu lệnh SELECT, chúng tôi đã thêm điều kiện sau:

product_name LIKE '%' + @name + '%'

Bằng cách này, stored procedure trả về các sản phẩm có giá niêm yết nằm trong phạm vi giá niêm yết tối thiểu và tối đa và tên sản phẩm cũng chứa một đoạn văn bản mà bạn truyền vào.

Khi stored procedure được thay đổi thành công, bạn có thể thực thi nó như sau:

EXEC uspFindProducts @min_list_price = 900, @max_list_price = 1000, @name = 'Trek';

Trong câu lệnh này, chúng tôi đã sử dụng stored procedure uspFindProducts để tìm sản phẩm có giá niêm yết nằm trong khoảng 900 và 1.000 và tên của chúng có chứa từ Trek.

Hình ảnh sau đây cho thấy đầu ra:

Tạo các tham số tùy chọn cho stored procedure trong SQL Server

Khi bạn thực thì stored procedure uspFindProducts, bạn phải truyền cả ba đối số tương ứng với ba tham số của stored procedure.

SQL Server cho phép bạn chỉ định các giá trị mặc định cho các tham số để khi bạn thực thi stored procedure, bạn có thể bỏ qua các tham số có giá trị mặc định.

Xem stored procedure sau đây:

ALTER PROCEDURE uspFindProducts ( @min_list_price AS DECIMAL = 0, @max_list_price AS DECIMAL = 999999, @name AS VARCHAR(max) ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND list_price <= @max_list_price AND product_name LIKE '%' + @name + '%' ORDER BY list_price; END;

Trong stored procedure này, chúng tôi đã gán 0 làm giá trị mặc định cho tham số @min_list_price và 999.999 làm giá trị mặc định cho tham số @max_list_price.

Khi stored procedure được biên dịch, bạn có thể thực thi nó mà không cần truyền các đối số cho tham số @min_list_price và @max_list_price:

EXEC uspFindProducts @name = 'Trek';

Đây là kết quả:

Trong trường hợp này, stored procedure đã sử dụng giá trị 0 cho tham số @min_list_price và giá trị 999.999 cho tham số @max_list_price khi nó thực thi truy vấn.

Các tham số @min_list_price và @max_list_price được gọi là tham số tùy chọn (optional parameters).

Tất nhiên, bạn cũng có thể truyền các đối số cho các tham số tùy chọn. Ví dụ: câu lệnh sau trả về tất cả các sản phẩm có giá niêm yết lớn hơn hoặc bằng 6.000 và tên chứa từ Trek:

EXEC uspFindProducts @min_list_price = 6000, @name = 'Trek';

Sử dụng NULL làm giá trị mặc định cho tham số của stored procedure trong SQL Server

Trong stored procedure uspFindProducts, chúng tôi đã sử dụng 999.999 làm giá niêm yết tối đa mặc định. Điều này không ổn vì trong tương lai bạn có thể có những sản phẩm có giá niêm yết lớn hơn thế.

Một kỹ thuật điển hình để tránh điều này là sử dụng NULL làm giá trị mặc định cho các tham số:

ALTER PROCEDURE uspFindProducts ( @min_list_price AS DECIMAL = 0, @max_list_price AS DECIMAL = NULL, @name AS VARCHAR(max) ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND (@max_list_price IS NULL OR list_price <= @max_list_price) AND product_name LIKE '%' + @name + '%' ORDER BY list_price; END;

Trong mệnh đề WHERE, chúng tôi đã thay đổi điều kiện để xử lý giá trị NULL cho tham số @max_list_price:

(@max_list_price IS NULL OR list_price <= @max_list_price)

Tips: đây là một biểu thức điều kiện rất hay mà bạn sẽ bắt gặp rất nhiều trong các dự án thực tế khi đi làm.

Câu lệnh sau đây thực thi stored procedure uspFindProducts để tìm sản phẩm có giá niêm yết lớn hơn hoặc bằng 500 và tên có chứa từ Haro.

EXEC uspFindProducts @min_list_price = 500, @name = 'Haro';

Trong phần này, bạn đã học cách tạo và thực thi stored procedure với một hoặc nhiều tham số. Bạn cũng đã học cách tạo các tham số tùy chọn và sử dụng NULL làm giá trị mặc định cho các tham số.

Biến trong SQL Server

Biến là một đối tượng chứa một giá trị duy nhất của một kiểu dữ liệu cụ thể

Tham số output của stored procedureTrong phần này, bạn sẽ tìm hiểu về biến trong SQL Server bao gồm khai báo biến, gán giá trị cho biến và gán các trường giá trị của bản ghi cho biến.

Biến là gì?

Biến là một đối tượng chứa một giá trị duy nhất của một kiểu dữ liệu cụ thể, ví dụ: integer, decimal, date hoặc chuỗi ký tự, v.v.

Chúng ta thường sử dụng biến trong các trường hợp sau:

• Là một bộ đếm vòng lặp để đếm số lần vòng lặp được thực hiện.
• Để giữ một giá trị được kiểm tra bằng câu lệnh vòng lặp, chẳng hạn như lệnh WHILE.
• Để lưu trữ giá trị được trả về bởi một stored procedure hoặc một hàm.

Khai báo biến

Để khai báo một biến, bạn sử dụng câu lệnh DECLARE. Ví dụ: câu lệnh sau khai báo một biến có tên @model_year:

DECLARE @model_year SMALLINT;

Câu lệnh DECLARE tạo một biến bằng cách gán cho nó một tên và kiểu dữ liệu. Tên biến phải bắt đầu bằng ký tự @. Trong ví dụ này, kiểu dữ liệu của biến @model_year là SMALLINT.

Theo mặc định, khi một biến được khai báo, giá trị của nó được đặt thành NULL.

Giữa tên biến và loại dữ liệu, bạn có thể sử dụng AStừ khóa tùy chọn như sau:

DECLARE @model_year AS SMALLINT;

Để khai báo nhiều biến, bạn phân tách các biến bằng dấu phẩy:

DECLARE @model_year SMALLINT, @product_name VARCHAR(MAX);

Gán giá trị cho biến

Để gán giá trị cho một biến, bạn sử dụng câu lệnh SET. Ví dụ: câu lệnh sau gán 2018 cho biến @model_year:

SET @model_year = 2018;

Sử dụng biến trong truy vấn

Câu lệnh SELECT sau sử dụng biến @model_year trong mệnh đề WHERE để tìm các sản phẩm theo năm của model:

SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER BY product_name;

Bây giờ, bạn có thể đặt mọi thứ lại với nhau và thực thi khối mã sau để có danh sách các sản phẩm có năm model là 2018:

DECLARE @model_year SMALLINT; SET @model_year = 2018; SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER BY product_name;

Lưu ý: để thực thi mã, bạn bấm vào nút Execute như trong hình sau, hoặc nhấn phím F5:

Hình ảnh sau đây cho thấy kết quả đầu ra:

Lưu trữ kết quả truy vấn trong biến

Các bước sau đây mô tả cách lưu trữ kết quả truy vấn trong một biến:

Đầu tiên, khai báo một biến có tên @product_count với kiểu dữ liệu integer:

DECLARE @product_count INT;

Thứ hai, sử dụng câu lệnh SET để gán tập kết quả của truy vấn cho biến:

SET @product_count = ( SELECT COUNT(*) FROM production.products );

Thứ ba, xuất nội dung của biến @product_count:

SELECT @product_count;

Hoặc bạn có thể sử dụng câu lệnh PRINT để in ra nội dung của một biến:

PRINT @product_count;

Hoặc là

PRINT 'The number of products is ' + CAST(@product_count AS VARCHAR(MAX));

Kết quả như sau:

The number of products is 204

Để ẩn số lượng bản ghi bị ảnh hưởng, bạn sử dụng câu lệnh sau:

SET NOCOUNT ON;

Select một bản ghi vào biến

Các bước sau minh họa cách khai báo hai biến, gán một bản ghi cho chúng và xuất nội dung của các biến:

Đầu tiên, khai báo các biến để lưu trữ tên sản phẩm và giá niêm yết:

DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL(10,2);

Thứ hai, gán tên cột cho các biến tương ứng:

SELECT @product_name = product_name, @list_price = list_price FROM production.products WHERE product_id = 100;

Thứ ba, xuất nội dung của các biến:

SELECT @product_name AS product_name, @list_price AS list_price;

Câu lệnh hoàn chỉnh sẽ như sau:

DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL(10,2); SELECT @product_name = product_name, @list_price = list_price FROM production.products WHERE product_id = 100; SELECT @product_name AS product_name, @list_price AS list_price;

Đây là kết quả:

Cộng dồn các giá trị vào một biến

Stored procedure sau đây có một tham số và trả về danh sách các sản phẩm dưới dạng chuỗi:

CREATE PROC uspGetProductList ( @model_year SMALLINT ) AS BEGIN DECLARE @product_list VARCHAR(MAX); SET @product_list = ''; SELECT @product_list = @product_list + product_name + CHAR(10) FROM production.products WHERE model_year = @model_year ORDER BY product_name; PRINT @product_list; END;

Trong stored procedure này:

• Đầu tiên, chúng tôi đã khai báo một biến có tên @product_list với kiểu varchar và gán giá trị của nó thành trống.
• Thứ hai, chúng tôi đã lấy danh sách tên sản phẩm từ bảng sản phẩm dựa vào tham số @model_year. Trong câu lệnh SELECT chúng tôi cộng dồn tên sản phẩm vào biến @product_list. Lưu ý rằng CHAR(10) trả về ký tự xuống dòng.
• Thứ ba, chúng tôi đã sử dụng câu lệnh PRINT để in ra danh sách tên sản phẩm.

Câu lệnh sau đây thực thi stored procedure uspGetProductList:

EXEC uspGetProductList 2018

Hình ảnh sau đây cho thấy một phần kết quả đầu ra:

Trong phần này, bạn đã tìm hiểu về biến trong SQL Server bao gồm khai báo biến, gán giá trị cho biến và gán các trường của bản ghi cho các biến.

Tham số OUTPUT của stored procedure trong SQL Server

Cách tạo tham số OUTPUT của stored procedure trong SQL Server như thế nào?

Trong phần này, bạn sẽ tìm hiểu cách sử dụng các tham số  OUTPUT (tham số  đầu ra) trong SQL Server để trả dữ liệu trở lại chương trình gọi.

Tạo tham số OUTPUT cho stored procedure

Để tạo một tham số OUTPUT cho stored procedure, bạn sử dụng cú pháp sau:

parameter_name data_type OUTPUT

Một stored procedure có thể có nhiều tham số OUTPUT. Ngoài ra, các tham số OUTPUT có thể ở bất kỳ kiểu dữ liệu hợp lệ nào như: số nguyên, ngày tháng và chuỗi.

Ví dụ: stored procedure sau đây tìm kiếm các sản phẩm theo năm model và trả về số lượng sản phẩm thông qua tham số OUTPUT là @product_count:

CREATE PROCEDURE uspFindProductByModel ( @model_year SMALLINT, @product_count INT OUTPUT ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE model_year = @model_year; SELECT @product_count = @@ROWCOUNT; END;

Trong stored procedure này:

Đầu tiên, chúng tôi đã tạo một tham số OUTPUT có tên là @product_count để lưu trữ số lượng sản phẩm được tìm thấy:

@product_count INT OUTPUT

Thứ hai, sau câu lệnh SELECT, chúng ta đã gán số lượng bản ghi được trả về bởi truy vấn (@@ROWCOUNT) cho tham số @product_count.

Lưu ý: @@ROWCOUNT là một biến hệ thống trả về số lượng bản ghi được đọc bởi câu lệnh phía trước.

Khi bạn thực hiện câu lệnh CREATE PROCEDURE trên, stored procedure uspFindProductByModel sẽ được biên dịch và lưu trong danh mục cơ sở dữ liệu.

Nếu mọi thứ đều ổn, SQL Server sẽ đưa ra thông báo sau:

Commands completed successfully.

Thực thi stored procedure với các tham số OUTPUT

Để thực thi stored procedure với các tham số OUTPUT, bạn làm theo các bước sau:

• Đầu tiên, khai báo các biến để lưu trữ các giá trị được trả về bởi các tham số OUTPUT.
• Thứ hai, sử dụng các biến này khi gọi thực thi stored procedure.

Ví dụ, câu lệnh sau thực hiện stored procedure uspFindProductByModel:

DECLARE @count INT; EXEC uspFindProductByModel @model_year = 2018, @product_count = @count OUTPUT; SELECT @count AS 'Number of products found';

Hình ảnh sau đây cho thấy đầu ra:

Trong ví dụ này:

Đầu tiên, chúng tôi khai báo biến @count để lưu trữ giá trị của tham số OUTPUT của stored procedure:

DECLARE @count INT;

Sau đó, thực thi stored procedure uspFindProductByModel và truyền các tham số:

EXEC uspFindProductByModel @model_year = 2018, @product_count = @count OUTPUT;

Trong câu lệnh này, tham số model_year là 2018 và biến @count sẽ được gán giá trị của tham số OUTPUT @product_count.

Bạn cũng có thể thực thi stored procedure uspFindProductByModel ngắn gọn như sau:

EXEC uspFindProductByModel 2018, @count OUTPUT;

Lưu ý: nếu bạn quên từ khóa OUTPUT sau biến @count thì giá trị của biến @count sẽ là NULL.

Cuối cùng, hiển thị giá trị của biến @count:

SELECT @count AS 'Number of products found';

Trong phần này, bạn đã học cách sử dụng tham số OUTPUT để truyền dữ liệu từ stored procedure trở lại chương trình gọi.

Câu lệnh BEGIN … END trong SQL Server

Câu lệnh BEGIN…END trong SQL Server được sử dụng để định nghĩa một khối câu lệnh

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh BEGIN…END trong SQL Server để bọc một tập hợp các câu lệnh Transact-SQL thành một khối câu lệnh.

Tổng quan về câu lệnh BEGIN…END trong SQL Server

Câu lệnh BEGIN…END trong SQL Server được sử dụng để định nghĩa một khối câu lệnh. Một khối câu lệnh bao gồm một tập hợp các câu lệnh SQL thực thi cùng nhau.

Nói cách khác, nếu xem mỗi câu lệnh là một câu văn thì câu lệnh BEGIN…END cho phép bạn định nghĩa một đoạn văn.

Sau đây minh họa cú pháp của câu lệnh BEGIN…END:

BEGIN { sql_statement | statement_block} END

Trong cú pháp này, bạn đặt một tập hợp các câu lệnh SQL giữa các từ khóa BEGIN và END, ví dụ:

BEGIN SELECT product_id, product_name FROM production.products WHERE list_price > 100000; IF @@ROWCOUNT = 0 PRINT 'No product with price greater than 100000 found'; END

Đầu ra:

Để xem các thông báo được tạo bởi câu lệnh PRINT, trong SQL Server Management Studio, bạn cần nhấp vào tab Messages. Theo mặc định, tab Results được bật và tab Messages bị ẩn.

Trong ví dụ này:

• Đầu tiên, chúng tôi có một khối lệnh bắt đầu bằng từ khóa BEGIN và kết thúc bằng từ khóa END.
• Thứ hai, bên trong khối lệnh, chúng tôi có một câu lệnh SELECT tìm kiếm các sản phẩm có giá niêm yết lớn hơn 100.000. Sau đó, chúng tôi có câu lệnh IF để kiểm tra xem truy vấn có trả về bất kỳ sản phẩm nào không và in ra một thông báo nếu không có sản phẩm nào trả về.

Lưu ý: @@ROWCOUNT là một biến hệ thống trả về số lượng bản ghi bị ảnh hưởng bởi câu lệnh phía trước.

Câu lệnh BEGIN… END giới hạn một khối logic của các câu lệnh SQL. Chúng tôi thường sử dụng câu lệnh  BEGIN…END ở đầu và cuối của một stored procedure và function.

Câu lệnh BEGIN…END cũng được sử dụng cho các câu lệnh IF ELSE, câu lệnh WHILE, v.v., để bao bọc nhiều câu lệnh.

Câu lệnh BEGIN … END lồng nhau trong SQL Server

Khối câu lệnh BEGIN…END có thể lồng nhau trong SQL Server. Nó đơn giản có nghĩa là bạn có thể đặt một câu lệnh BEGIN…END trong một câu lệnhBEGIN… END khác .

Hãy xem xét ví dụ sau:

BEGIN DECLARE @name VARCHAR(MAX); SELECT TOP 1 @name = product_name FROM production.products ORDER BY list_price DESC; IF @@ROWCOUNT <> 0 BEGIN PRINT 'The most expensive product is ' + @name END ELSE BEGIN PRINT 'No product found'; END; END

Trong ví dụ này, chúng tôi đã sử dụng câu lệnh BEGIN…END để bọc toàn bộ khối câu lệnh. Bên trong khối này, chúng tôi cũng sử dụng BEGIN…END cho các câu lệnh IF…ELSE.

Trong phần này, bạn đã tìm hiểu về câu lệnh BEGIN…END trong SQL Server để bọc các câu lệnh Transact-SQL thành các khối lệnh.

CURSOR trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng CURSOR trong SQL Server để xử lý một tập kết quả, mỗi lần một bản ghi.

SQL Server hoạt động dựa trên tập hợp, ví dụ, câu lệnh SELECT trả về một tập hợp các bản ghi được gọi là tập kết quả. Tuy nhiên, đôi khi, bạn có thể muốn xử lý một tập dữ liệu theo từng bản ghi. Đây là lúc CURSOR được sử dụng.

CURSOR là gì?

CURSOR (con trỏ) là một đối tượng cho phép duyệt qua các bản ghi của tập kết quả. Nó cho phép bạn xử lý từng bản ghi riêng lẻ được trả về bởi một truy vấn.

Vòng đời của CURSOR trong SQL Server

Đây là các bước để tạo và sử dụng một CURSOR:

Đầu tiên, khai báo một CURSOR.

DECLARE cursor_name CURSOR FOR select_statement;

Để khai báo một CURSOR, bạn chỉ định tên của nó sau từ khóa DECLARE với kiểu dữ liệu là CURSOR và cung cấp một câu lệnh SELECT định nghĩa tập kết quả cho CURSOR.

Tiếp theo, mở CURSOR để thực thi câu lệnh SELECT:

OPEN cursor_name;

Sau đó, duyệt từng bản ghi trong CURSOR và đưa vào một hoặc nhiều biến:

FETCH NEXT FROM cursor INTO variable_list;

SQL Server cung cấp biến hệ thống @@FETCHSTATUS trả về trạng thái của câu lệnh FETCH. Nếu @@FETCHSTATUS trả về 0, có nghĩa là câu lệnh FETCH đã thành công. Bạn có thể sử dụng câu lệnh WHILE để duyệt tất cả các bản ghi từ CURSOR như trong đoạn mã sau:

WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cursor_name; END;

Sau khi đã duyệt qua tất cả bản ghi trong CURSOR, chúng ta sẽ đóng CURSOR bằng lệnh sau:

CLOSE cursor_name;

Cuối cùng, phân bổ CURSOR để giải phóng nó:

DEALLOCATE cursor_name;

Ví dụ CURSOR trong SQL Server

Chúng tôi sẽ sử dụng bảng products trong cơ sở dữ liệu mẫu BikeStores để minh họa cho CURSOR trong SQL Server:

Đầu tiên, khai báo hai biến để lưu trữ tên sản phẩm và giá niêm yết và một CURSOR để lưu trữ kết quả của truy vấn lấy tên sản phẩm và giá niêm yết từ bảng production.products:

DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL; DECLARE cursor_product CURSOR FOR SELECT product_name, list_price FROM production.products;

Tiếp theo, mở CURSOR:

OPEN cursor_product;

Sau đó, duyệt từng bản ghi từ CURSOR và in ra tên sản phẩm và giá niêm yết:

FETCH NEXT FROM cursor_product INTO @product_name, @list_price; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @product_name + CAST(@list_price AS varchar); FETCH NEXT FROM cursor_product INTO @product_name, @list_price; END;

Sau đó, đóng CURSOR:

CLOSE cursor_product;

Cuối cùng, phân bổ CURSOR để giải phóng nó.

DEALLOCATE cursor_product;

Đây là đoạn mã hoàn chỉnh:

DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL; DECLARE cursor_product CURSOR FOR SELECT product_name, list_price FROM production.products; OPEN cursor_product; FETCH NEXT FROM cursor_product INTO @product_name, @list_price; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @product_name + CAST(@list_price AS varchar); FETCH NEXT FROM cursor_product INTO @product_name, @list_price; END; CLOSE cursor_product; DEALLOCATE cursor_product;

Đây là đầu ra một phần:

Trong thực tế, bạn sẽ hiếm khi sử dụng CURSOR để xử lý tập kết quả vì nó rất chậm.

Trong phần này, bạn đã học cách sử dụng CURSOR trong SQL Server để xử lý một tập kết quả, mỗi lần một bản ghi.

@@rowcount là gì?

@@rowcount được sử dụng để lưu trữ tổng só dòng bị ảnh hưởng bởi câu truy vấn cuối cùng trong SQL server

@@rowcount được sử dụng để lưu trữ tổng só dòng bị ảnh hưởng bởi câu truy vấn cuối cùng trong SQL server. Ví dụ:

SELECT @CUSTOMER_COUNT = @@ROWCOUNT;

Ý nghĩa: gán tổng số dòng vào CUSTOMER_COUNT

Nguyên tắc viết viết stored procedure trong sql

Viết stored procedure trong SQL là một cách để tổ chức và lưu trữ một loạt các câu lệnh SQL để thực thi công việc cụ thể

• Đặt tên có ý nghĩa:

Chọn tên cho stored procedure mô tả chức năng hoặc nhiệm vụ mà nó thực hiện.

Tránh sử dụng các tên quá ngắn hoặc không rõ ràng.

• Tham số (nếu cần):

Nếu stored procedure của bạn cần nhận tham số đầu vào, hãy sử dụng chúng để làm cho stored procedure linh hoạt hơn.

Chú ý đặt tên tham số sao cho dễ hiểu.

• Sử dụng cấu trúc điều khiển và vòng lặp:

Sử dụng các cấu trúc điều khiển như IF, ELSEIF, ELSE và các vòng lặp để quản lý luồng điều khiển trong stored procedure.

• Tránh sử dụng quá nhiều lệnh GO:

Lệnh GO được sử dụng để phân tách các tập lệnh trong SQL Server Management Studio. Tuy nhiên, không phải tất cả các hệ thống SQL đều hỗ trợ lệnh này và nó không phải là một phần của chuẩn SQL.

• Quản lý lỗi và xử lý ngoại lệ:

Sử dụng cơ chế xử lý lỗi như TRY…CATCH để quản lý lỗi và cung cấp thông báo lỗi hợp lý cho người sử dụng hoặc những phần mềm gọi stored procedure.

• Tối ưu hiệu suất:

Cân nhắc về hiệu suất khi viết stored procedure. Tránh sử dụng quá nhiều tài nguyên và tối ưu hóa câu lệnh SQL bên trong stored procedure.

• Bảo mật:

Cân nhắc về bảo mật khi viết stored procedure. Tránh sử dụng các tham số đầu vào mà không kiểm tra và xử lý chúng, để tránh tấn công SQL Injection.

• Chia nhỏ và tái sử dụng:

Nếu có thể, chia stored procedure thành các phần nhỏ hơn và có thể tái sử dụng ở nhiều địa điểm trong ứng dụng của bạn.

Lệnh print trong SQL server

Lệnh PRINT được sử dụng để hiển thị thông điệp hoặc giá trị trong cửa sổ kết quả của môi trường thực thi SQL Server

Lệnh PRINT được sử dụng để hiển thị thông điệp hoặc giá trị trong cửa sổ kết quả của môi trường thực thi SQL Server Management Studio (SSMS).

Cú pháp cơ bản của lệnh PRINT như sau:

PRINT ‘Nội dung hoặc giá trị muốn hiển thị’;

Khi bạn thực thi một batch hoặc một stored procedure chứa lệnh PRINT, thông điệp sẽ xuất hiện trong cửa sổ kết quả của SSMS. Điều này có thể hữu ích khi bạn đang phát triển hoặc debug các đoạn mã SQL để theo dõi giá trị của biến, thông báo lỗi, hoặc các sự kiện quan trọng khác.

Set nocount on trong SQL server

SET NOCOUNT ON là một tùy chọn thực thi mà khi được kích hoạt, nó sẽ ngăn chặn hiển thị số hàng bị ảnh hưởng (number of rows affected) trong cửa sổ kết quả của một batch hoặc một stored procedure. Khi SET NOCOUNT ON được sử dụng, không có thông báo số hàng bị ảnh hưởng nào sẽ được hiển thị trong cửa sổ kết quả.

Cú pháp:

SET NOCOUNT ON;

Ví dụ, khi bạn tạo một stored procedure, có thể sử dụng SET NOCOUNT ON để ngăn chặn hiển thị số hàng bị ảnh hưởng:

CREATE PROCEDURE dbo.MyProcedure AS BEGIN     SET NOCOUNT ON;     — Các câu lệnh SQL của stored procedure ở đây END;

Kết lại

Trên đây là toàn bộ chia sẻ của Comdy về stored procedure trong sql. Hi vọng những thông tin này là bổ ích và có ý nghĩa với mọi người. Xin cám ơn.