Lấy Về Giá Trị Từ Thủ Tục Bằng Output Parameter

Một tình huống tương đối thường gặp là bạn cần viết một thủ tục để ghi vào một bản ghi mới (ví dụ khách hàng), và trả về ID của bản ghi vừa được tạo (ID của khách hàng mới đó). SQL Server cung cấp một loại tham số gọi là output parameter dùng với thủ tục, để dùng trong tình huống trên. Để có thể dùng được output parameter cần liên quan đến cả hai phía: viết thủ tục và thực thi thủ tục.
Khi viết thủ tục, ở đoạn khai báo các tham số bạn cần đưa từ khóa “OUTPUT” vào sau tham số đó. Ở ví dụ sau, tham số @ID được khai báo là output parameter:

CREATE PROC dbo.ThemKhachHang
@Ten NVARCHAR(100),
@ID INT OUTPUT
AS
...

Đồng thời khi thực thi thủ tục bạn cũng cần đưa từ khóa “OUTPUT” vào tham số tương ứng trong lời gọi thủ tục:

DECLARE @ID INT
EXEC dbo.ThemKhachHang @Ten='Nguyen Van A', @ID OUTPUT
...

Dưới đây là ví dụ hoàn chỉnh:

CREATE TABLE dbo.KhachHang (KH_ID INT PRIMARY KEY IDENTITY, KH_Ten NVARCHAR(100))
GO
CREATE PROC dbo.ThemKhachHang
@Ten NVARCHAR(100),
@ID INT OUTPUT
AS
BEGIN
 INSERT dbo.KhachHang(KH_Ten) VALUES(@Ten)
 SET @ID = SCOPE_IDENTITY()
END
 
-- Gọi thủ tục
DECLARE @KH_ID INT
EXEC dbo.ThemKhachHang 'Nguyen Van A', @KH_ID OUTPUT
PRINT 'Khach hang ID:'
PRINT @KH_ID

Trong ví dụ trên tôi gọi thủ tục từ bên trong SQL Server. Khi gọi thủ tục từ ứng dụng (ví dụ .Net) công việc cũng tương tự như vậy: Bạn cũng cần khai báo một biến và truyền cho thủ tục với lựa chọn là output parameter; Khi thực hiện xong, biến đó sẽ chứa giá trị mà thủ tục gửi trả lại.

Bàn thêm về các cách làm khác
Nói chung khi muốn thủ tục gửi về một hoặc vài giá trị, cách làm dùng output parameter là thích hợp nhất. Một vài cách làm khác là dùng lệnh RETURN hoặc SELECT ở cuối thủ tục.
Bạn có thể đưa lệnh RETURN @ID vào cuối thủ tục trên và ứng dụng cũng sẽ nhận được giá trị @ID. Nhưng cách này chỉ có thể trả về một giá trị. Khi bạn cần lấy về hai giá trị hoặc nhiều hơn nữa thì nó không đáp ứng được. Ngoài ra có một lý do khác không nên dùng RETURN (và đây là lý do chính), là lệnh RETURN được tạo ra với mục đích trả về trạng thái kết quả (status) của thủ tục, ví dụ 0 là bình thường, nhỏ hơn 0 là lỗi… Trong một tình huống nào đó giá trị cần trả về là số âm, có thể ứng dụng sẽ nghĩ thủ tục đã có lỗi, mặc dù nó thực hiện thành công. Vì thế bạn không nên lạm dụng (overload) lệnh RETURN cho các mục đích khác. Microsoft cũng khuyến cáo không nên dùng RETURN để trả về dữ liệu.

Bạn cũng có thể đưa lệnh SELECT @ID vào cuối thủ tục, nó sẽ gửi về một tập kết quả chứa một bản ghi. Ở phía ứng dụng bạn cũng cần viết thêm code để đọc dữ liệu từ tập bản ghi này. Cách làm này tốn kém hơn vì tập bản ghi có overhead lớn hơn là một (hoặc vài) giá trị đơn lẻ, phía ứng dụng cũng cần nhiều code xử lý hơn. Nói chung khi chỉ cần lấy về một vài giá trị, bạn nên dùng output parameter. Còn khi có quá nhiều giá trị cần trả về, output parameter trở nên rườm rà quá thì bạn có thể chuyển sang dùng SELECT.

Sử Dụng Mệnh Đề OUTPUT Trong Các Lệnh DML

Mệnh đề OUTPUT bắt đầu được đưa ra từ SQL Server 2005 trong các lệnh DELETE, INSERT, UPDATE, để trả về (hoặc lưu vào một bảng khác) các bản ghi bị ảnh hưởng. Tính năng này rất hữu dụng khi bạn muốn lưu lại, chẳng hạn với lệnh DELETE, các bản ghi cần xóa sang một nơi khác để có thể tra cứu về sau. Ví dụ bạn có bảng KhachHang chứa các khách hàng hiện tại, và KhachHangLuu chứa các thông tin cũ của khách hàng mỗi khi có sửa đổi:

CREATE TABLE dbo.KhachHang(
KhachHang_ID INT IDENTITY PRIMARY KEY,
Ten NVARCHAR(100),
Email VARCHAR(100)
)
 
CREATE TABLE dbo.KhachHangLuu(
KhachHang_ID INT,
Ten NVARCHAR(50),
Email VARCHAR(100)
)
GO
INSERT INTO dbo.KhachHang VALUES(N'Ý Lan','ylan@ylan.com')
INSERT INTO dbo.KhachHang VALUES(N'Tuấn Ngọc','tuanngoc@tuanngoc.com')
INSERT INTO dbo.KhachHang VALUES(N'Thái Hiền','thaihien@thaihien.com')
INSERT INTO dbo.KhachHang VALUES(N'Ngọc Hạ','ngocha@ngocha.com')
 
SELECT * FROM dbo.KhachHang
 
-- xóa KhachHang_ID = 4 (Ngọc Hạ)
DELETE dbo.KhachHang
OUTPUT DELETED.* INTO dbo.KhachHangLuu
WHERE KhachHang_ID = 4
 
-- cập nhật email của KhachHang_ID = 3
UPDATE dbo.KhachHang
OUTPUT DELETED.* INTO dbo.KhachHangLuu
SET Email = N'me@thaihien.com'
WHERE KhachHang_ID = 3
 
SELECT * FROM dbo.KhachHangLuu

Như vậy là bạn luôn có bản lưu chứa thông tin cũ của khách hàng trên bảng KhachHangLuu mỗi khi có sửa đổi trên bảng chính. Với SQL Server 2000 bạn phải thực hiện điều trên bằng hai lệnh, INSERT vào bảng lưu rồi thực hiện DELETE/UPDATE trên bảng chính, và để đảm bảo tính toàn vẹn bạn phải đưa hai lệnh vào một transaction. Với mệnh đề OUTPUT chỉ có một lệnh được thực hiện, và bản thân nó là một transaction nên bạn khỏi cần bận tâm về điều đó.

Một ứng dụng khác của mệnh đề OUTPUT là bạn có thể dùng nó để lấy về giá trị IDENTITY vừa được INSERT vào bảng:

INSERT INTO dbo.KhachHang
OUTPUT INSERTED.KhachHang_ID
VALUES(N'Elvis Phương','phuong@ElvisPhuong.com')
 
KhachHang_ID
------------
5

COMPARE DELETE & TRUNCATE

- SQL Server cung cấp 2 phương pháp để xóa dữ liệu, DELETE và TRUNCATE. Cú pháp của hai lệnh này như sau:

Tuy cùng để xóa dữ liệu, nhưng hai lệnh này có những khác nhau cơ bản:

• DELETE cung cấp các lựa chọn để xóa những dòng dữ liệu thỏa mãn các điều kiện nhất định, như WHERE hoặc JOIN với các bảng khác.
  -> TRUNCATE không có lựa chọn nào, mà luôn cắt bỏ toàn bộ dữ liệu của bảng. Nói cách khác, ta không thể TRUNCATE 1 nửa hay 1 phần của bảng.

• DELETE hỗ trợ transaction. Khi lệnh DELETE nằm trong 1 transaction và trong một tình huống nào đó transaction được ROLLBACK thì các bản ghi bị xóa bởi lệnh DELETE sẽ trở lại bảng không có gì suy xuyển.
  -> TRUNCATE thì ngược lại, không hỗ trợ transaction. Một khi đã thực hiện thì không thể lấy lại dữ liệu được nữa.

• DELETE khi thực hiện bao gồm quá trình tìm các bản ghi thỏa mãn điều kiện của câu lệnh, và xóa các bản ghi này. Việc tìm các bản ghi cần xóa được thực hiện giống hệt như một câu lệnh SELECT, cũng tối ưu hóa, lựa chọn giữa các phương án thực hiện khác nhau và chọn ra phương án tối ưu (dựa vào index, statistics…).
  -> TRUNCATE thì chỉ có một phương án thực hiện duy nhất, đó là cắt bỏ tất cả các dòng dữ liệu của bảng.

• Với DELETE, các bản ghi bị xóa sẽ được kiểm tra xem có vi phạm ràng buộc FOREIGN KEY không. Ví dụ ta có hai bảng MAT_HANG và BAN_HANG là quan hệ 1-n thông qua MA_MH; nếu MA_MH=1 đã có giao dịch, nghĩa là bảng BAN_HANG đã có bản ghi với MA_MH=1, thì khi DELETE bản ghi với MA_MH=1 từ bảng MAT_HANG (bảng cha) SQL SERVER sẽ báo lỗi và không cho xóa.
  Nếu trước đó, khi ta định nghĩa ràng buộc FOREIGN KEY mà có lựa chọn CASCADE DELETE, thì thay vì báo lỗi SQL Server sẽ đồng thời xóa hết các bản ghi trong cả bảng BAN_HANG với MA_MH=1.
• TRUNCATE thì không có những đoạn kiểm tra dài dòng như thế. Nếu bảng có ràng buộc FOREIGN KEY, SQL Server sẽ báo lỗi và không cho thực hiện (nhớ là lựa chọn CASCADE DELETE trong khai báo FOREIGN KEY chỉ ảnh hưởng đến lệnh DELETE chứ không tác dụng đối với TRUNCATE).

• Vì DELETE hỗ trợ transaction và dùng transaction log, nó có thể dùng với bảng nằm trong một replication hoặc database có dùng log shipping.
  -> TRUNCATE thì vì không ghi gì vào transaction log nên khi gặp một trong các tình huống trên sẽ bị từ chối ngay.

• Với DELETE, nếu bảng có index thì các index cũng sẽ được cập nhật để xóa đi các node tương ứng với các bản ghi bị xóa.
  TRUNCATE thì rất đơn giản, các index của bảng cũng bị cắt cụt theo.

• DELETE không ảnh hưởng đến giá trị IDENTITY. Nếu bảng có 100 bản ghi và cột IDENTITY có giá trị từ 1-100; nay ta DELETE bản ghi có cột IDENTITY=100 rồi INSERT một bản ghi mới; bản ghi mới sẽ có cột IDENTITY=101.
  TRUNCATE luôn đặt lại IDENTITY trở về 1. Bản ghi đầu tiên được INSERT sau khi TRUNCATE sẽ có cột IDENTITY=1.

• DELETE thực ra chỉ đánh dấu xóa các bản ghi chứ ngay sau đó dữ liệu của các bản ghi bị xóa vẫn nằm nguyên tại chỗ. Dần dần khi ta INSERT thêm dữ liệu vào bảng thì các bản ghi mới sẽ ghi đè lên các vùng lưu trữ đó. Ta có thể kiểm tra để thấy kích thước bảng không thay đổi ngay cả sau khi chạy DELETE FROM TblName (xóa hết các bản ghi).
  TRUNCATE thì xóa hết dữ liệu đồng thời giải phóng vùng lưu trữ giành cho bảng, trả lại cho SQL Server. Ta có thể so sánh DELETE như là xóa file, còn TRUNCATE thì như format lại ổ cứng.

• DELETE cho phép áp dụng đối với bảng ở server khác được nối qua linked server.
  TRUNCATE không cho phép điều này, bạn chỉ có thể TRUNCATE bảng nằm trên cùng server.

Vì những lý do trên, DELETE luôn luôn chậm hơn TRUNCATE. Càng có nhiều bản ghi DELETE càng chậm, còn TRUNCATE thì không phụ thuộc vào lượng dữ liệu. DELETE có phạm vi ứng dụng rộng hơn; còn TRUNCATE chỉ dùng được mỗi một việc, nhưng nó lại làm rất nhanh. Vì vậy, hãy nhớ dùng TRUNCATE khi có thể được.

Đặt Lại Giá Trị Của Trường IDENTITY

Khi bạn TRUNCATE bảng, trường IDENTITY sẽ được reset trở về giá trị ban đầu (khi tạo bảng). Tuy nhiên có những trường hợp bạn không thể dùng TRUNCATE. Ví dụ bạn muốn đặt lại giá trị của trường IDENTITY trong bảng dbo.TableName về 1, bạn có thể dùng lệnh này:

DBCC CHECKIDENT ('dbo.TableName', RESEED, 1)

SỬ DỤNG IDENTIFY TRONG SQL

1. Cột IDENTITY dùng để tạo một dãy số liên tục tăng cho mỗi bản ghi được INSERT.Trong rất nhiều trường hợp, cột IDENTITY cũng được dùng luôn làm khóa chính cho bảng.

Bạn khai báo thuộc tính IDENTITY cho cột như sau:

--Tạo bảng
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(1,1),
Col1 INT,
Col2 VARCHAR(50)...
)
--Thêm cột vào bảng
ALTER TABLE dbo.Tblyyy ADD ID_Col INT IDENTITY(1,1)

2. Cú pháp tổng quát của IDENTITY là:
IDENTITY(seed, increment)
trong đó seed là giá trị khởi tạo và increment là giá trị tăng cho mỗi lần. Khi không chỉ định seed và increment thì các giá trị mặc định của chúng được dùng là seed = 1 và increment = 1. Ví dụ:

--giá trị khởi đầu = 1 và mỗi lần tăng 1
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY...
)
 
--giá trị khởi đầu = 1000 và mỗi lần tăng 5
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(1000,5)...
)
 
-- giá trị khởi đầu = -100 và mỗi lần giảm 2
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(-100,-2)...
)

3. Bạn không thể thay đổi một cột có sẵn để nó trở thành cột IDENTITY, hoặc bỏ thuộc tính IDENTITY của nó. Khi bạn edit bảng trong Management Studio và sửa một cột thành IDENTITY hoặc ngược lại, ở phía sau thực ra SQL Server xóa bảng và tạo một bảng mới.

4. SQL Server không duy trì tính liên tục của dãy số IDENTITY, khi bạn DELETE bản ghi thì khoảng trống sẽ xuất hiện. Ở lần INSERT sau đó một giá trị mới sẽ được dùng, thay vì giá trị bị xóa được dùng lại.

5. Bản thân thuộc tính IDENTITY không đảm bảo các giá trị trong cột là duy nhất. Ví dụ, bạn có đặt lại seed với một giá trị tùy ý (kể cả giá trị đã được dùng trước đó). Khi cần đảm bảo tính duy nhất, bạn cần thêm ràng buộc khóa chính hoặc khóa duy nhất cho cột.

6. Để khởi tạo lại giá trị seed, bạn dùng lệnh: DBCC CHECKIDENT ('dbo.TableName', RESEED, 1)

7. Khi cần INSERT vào bảng, bạn có thể bỏ qua cột IDENTITY trong danh sách các cột, và giá trị của cột IDENTITY sẽ được tự động sinh ra (đó là mục đích chính khi tạo cột này)

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY,
Col_1 INT,
Col_2 VARCHAR(50)
)
GO
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(62,'def')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(37,'ghi')

Khi bạn muốn ghi đè lên giá trị tự sinh của cột IDENTITY, bạn phải đưa cột này vào danh sách các cột đồng thời trước đó phải đặt lại lựa chọn IDENTITY_INSERT là ON (mặc định là OFF)

SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO dbo.MyTable(ID_Col,Col_1,Col_2) VALUES(100,42,'jkl')

Một tình huống thường xảy ra là bạn INSERT vào bảng từ một bảng khác có cùng cấu trúc. Một cách lười biếng bạn có thể viết thế này:

INSERT INTO dbo.Table1 SELECT * FROM dbo.Table2

Khi bảng dbo.Table1 không có cột IDENTITY, câu lệnh trên thực hiện không có gì trở ngại. Nhưng nếu bảng dbo.Table1 có chứa cột IDENTITY, bạn sẽ nhận được thông báo lỗi sau:

An explicit value for the identity column in table 'dbo.Table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Như vậy theo nội dung của thông báo lỗi, để thực hiện được câu lệnh INSERT trên bạn cần thực hiện hai việc: đặt IDENTITY_INSERT là ON cho bảng dbo.Table1, và liệt kê ra các cột:

SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1(ID_Col, Col_1,...)
SELECT ID_Col, Col_1,...
FROM dbo.Table2

8. Một bảng chỉ được phép có tối đa một cột IDENTITY. Bạn có thể tìm ra cột nào là IDENTITY bằng cách edit bảng trong Management Studio, hoặc bằng cách truy vấn view hệ thống SYS.IDENTITY_COLUMNS:

SELECT *
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_ID = OBJECT_ID('dbo.Tblxxx')

9. Để lấy giá trị IDENTITY cuối cùng vừa được INSERT, bạn dùng hàm SCOPE_IDENTITY(), gọi hàm này ở ngay dưới câu lệnh INSERT.

INSERT INTO dbo.Tblxxx(Col_1,Col_2) VALUES(15,'abc')
SELECT SCOPE_IDENTITY()

Ngoài ra còn một vài hàm khác có cùng mục đích, nhưng khi đi vào chi tiết chúng có những khác biệt đáng kể. Trong phần lớn trường hợp, SCOPE_IDENTITY() là hàm an toàn nhất tránh được những rắc rối không cần thiết. Bạn có thể xem một bài so sánh về các hàm này.

10. Với các tính năng như trên, cột IDENTITY sẽ gây ra khó khăn trong một số trường hợp. Nếu bạn muốn duy trì tính liên tục của dãy số (ví dụ, dùng lại các giá trị bị xóa trước đó), bạn phải tự thực hiện việc kiểm tra này trước mỗi lần INSERT. Việc xử lý cũng phức tạp hơn khi bảng nằm trong một replication với nhiều publisher cùng update dữ liệu về một subscriber. Đặc biệt với replication hai chiều (hai database update qua lại cho nhau) thì nó hoàn toàn bó tay. Lý do là vì giá trị IDENTITY kế tiếp chỉ được lưu cục bộ ở mỗi database, bảng này không biết giá trị kế tiếp của bảng ở bên database kia là bao nhiêu, cho nên khi replication xảy ra thì các giá trị INSERT vào mỗi bảng sẽ bị xung đột với nhau.