- [C#] Thông báo page Facebook Trang Lập Trình VB.NET, C# bị Hack
- [C#] Chia sẻ source code phần mềm đếm số trang tập tin file PDF
- [C#] Cách Sử Dụng DeviceId trong C# Để Tạo Khóa Cho Ứng Dụng
- [SQLSERVER] Loại bỏ Restricted User trên database MSSQL
- [C#] Hướng dẫn tạo mã QRcode Style trên winform
- [C#] Hướng dẫn sử dụng temp mail service api trên winform
- [C#] Hướng dẫn tạo mã thanh toán VietQR Pay không sử dụng API trên winform
- [C#] Hướng Dẫn Tạo Windows Service Đơn Giản Bằng Topshelf
- [C#] Chia sẻ source code đọc dữ liệu từ Google Sheet trên winform
- [C#] Chia sẻ source code tạo mã QR MOMO đa năng Winform
- [C#] Chia sẻ source code phần mềm lên lịch tự động chạy ứng dụng Scheduler Task Winform
- [Phần mềm] Tải và cài đặt phần mềm Sublime Text 4180 full version
- [C#] Hướng dẫn download file từ Minio Server Winform
- [C#] Hướng dẫn đăng nhập zalo login sử dụng API v4 trên winform
- [SOFTWARE] Phần mềm gởi tin nhắn Zalo Marketing Pro giá rẻ mềm nhất thị trường
- [C#] Việt hóa Text Button trên MessageBox Dialog Winform
- [DEVEXPRESS] Chia sẻ code các tạo report in nhiều hóa đơn trên XtraReport C#
- [POWER AUTOMATE] Hướng dẫn gởi tin nhắn zalo từ file Excel - No code
- [C#] Chia sẻ code lock và unlock user trong domain Window
- [DEVEXPRESS] Vẽ Biểu Đồ Stock Chứng Khoán - Công Cụ Thiết Yếu Cho Nhà Đầu Tư trên Winform
[DATABASE] Hướng dẫn cách viết Dynamic Sql trong Sqlserver
Bài viết hôm nay, mình sẽ hướng dẫn các bạn cách viết một câu lệnh Dynamic SQL (sql động) trong cơ sở dữ liệu Microsoft Sqlserver.
[DATABASE] DYNAMIC SQL
Vậy Dynamic Sql là gì?
Khi bạn cần viết một thủ tục, trong đó tùy thuộc vào giá trị của các tham số đầu vào mà câu lệnh SQL cần thực hiện sẽ thay đổi, bạn cần tạo lập chuỗi lệnh SQL trong chương trình và thực thi chuỗi này. Chuỗi lệnh SQL đó được gọi là sql động.
SQL động giúp lập trình viên linh động hơn trong việc thực thi các câu lệnh SQL, tuy nhiên Dynamic SQL dễ bị dính lỗi sql injection hơn và phải biên dịch lại code.
Mình ví dụ như hình ảnh bên dưới:
Các bạn có thể nhìn thấy giao diện tìm kiếm ở bên trên:
Người dùng có thể tùy chọn các thông tin khác nhau: thành phố, quận huyện, diện tích, giá tiền, từ ngày đến ngày hoặc theo từ khóa.
Vậy thì mỗi người chọn sẽ phát sinh ra một câu lệnh Sql khác nhau.
Trong website trên mình sử dụng là MYSQL, nên mình sẽ show code demo cho các bạn xem tham khảo, rồi mình sẽ nói về thằng Microsoft Sqlsever cũng tương tự.
Code tìm kiếm nhà đất bằng MYSQL:
CREATE PROCEDURE `sp_find_tindang`(
type_id VARCHAR(10),
cate_id VARCHAR(10),
province_id VARCHAR(10),
district_id VARCHAR(10),
s_dientich VARCHAR(10),
e_dientich VARCHAR(10),
s_gia VARCHAR(10),
e_gia VARCHAR(10),
s_ngay date,
e_ngay date,
keywords VARCHAR(200) CHARSET utf8
)
BEGIN
-- DECLARE @sql VARCHAR(1000);
SET @sql = "SELECT *, DATE_FORMAT(ngaydang, '%d/%m/%Y') as ngaydang_text FROM tbl_tinrao WHERE 1=1";
SET @querytext = "Tìm kiếm tất cả bất động sản ";
IF (type_id <> "") THEN
SET @sql = CONCAT(@sql, " AND type_id = ", type_id);
END IF;
IF (cate_id <> "") THEN
SET @sql = CONCAT(@sql, " AND cate_id = ", cate_id);
SET @querytext = "";
select concat(@querytext, name) into @querytext from tbl_cate where id= cate_id;
END IF;
IF (province_id <> "") THEN
SET @sql = CONCAT(@sql, " AND province_id = ", province_id);
select concat(@querytext, " tại: " , name) into @querytext from province where id= province_id;
END IF;
IF (district_id <> "") THEN
SET @sql = CONCAT(@sql, " AND district_id = ", district_id);
select concat(@querytext, " - " , name) into @querytext from district where id= district_id;
END IF;
IF (s_dientich <> "") THEN
SET @sql = CONCAT(@sql, " AND dientich BETWEEN ", s_dientich, " AND ", e_dientich);
END IF;
IF (s_gia <> "") THEN
SET s_gia = concat(s_gia, '000000');
SET e_gia = concat(e_gia, '000000');
SET @sql = CONCAT(@sql, " AND gia BETWEEN ", s_gia, " AND ", e_gia);
END IF;
IF s_ngay <> "" and e_ngay = "" THEN
SET @sql = CONCAT(@sql, " AND ngaydang BETWEEN '", s_ngay, "' AND '", CURDATE(), "'");
ELSEIF s_ngay <> "" and e_ngay <> "" THEN
SET @sql = CONCAT(@sql, " AND ngaydang BETWEEN '", s_ngay, "' AND '", e_ngay, "'");
END IF;
IF (keywords <> "") THEN
SET @sql = CONCAT(@sql, " AND mota like '%", keywords,"%'");
if @querytext = "Tìm kiếm tất cả bất động sản " then
SET @querytext = concat("Từ khóa: ", keywords);
end if;
END IF;
select @sql as _sql, @querytext as querytext;
-- PREPARE stmt FROM @sql;
-- EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;
END
- Dynamic Sql trong Sqlserver: Tìm kiếm với nhiều tham số
Khi ta cần viết một thủ tục để tìm kiếm dữ liệu dựa vào các tham số đầu vào, ta có thể hình dung ra logic sẽ như sau:
IF @Param1 IS NOT NULL
SELECT... FROM dbo.Tblxxx WHERE Col1= @Param1
ELSE
SELECT TOP 200 ... FROM dbo.Tblxxx -- TOP 200 để khống chế số bản ghi khi không có tham số
Tức là khi tham số vào @Param1 được truyền giá trị thì ta lọc các bản ghi dựa trên giá trị đó, còn nếu không (NULL) thì ta không lọc. Tuy nhiên cách làm trên không thể mở rộng với nhiều tham số, vì số nhánh chương trình sẽ tăng rất nhanh (2^n). Ví dụ nếu ta có hai tham số @Param1 và @Param2, đoạn code sẽ giống như thế này:
IF (@Param1 IS NOT NULL) AND (@Param2 IS NOT NULL)
...
ELSE IF (@Param1 IS NOT NULL) AND (@Param2 IS NULL)
...
ELSE IF (@Param1 IS NULL) AND (@Param2 IS NOT NULL)
...
ELSE
...
Không những code rất cồng kềnh mà nó còn rất khó bảo trì. Nếu đến một lúc ta cần thêm một tham số thứ ba @Param3, sẽ tốn rất nhiều công để sửa lại và viết thêm vào đoạn code trên. Hoặc nếu cần thêm một cột vào kết quả đầu ra, ta sẽ phải thêm vào tất cả các nhánh của chương trình. Có thể nói cách làm trên là không khả thi trong đa số trường hợp.
Bài viết này giới thiệu hai cách làm có thể áp dụng trên thực tế, nhưng trước hết tôi nói qua về ví dụ sẽ được sử dụng trong bài.Ta có một database về các bộ phim đã được sản xuất, và giả sử ta cho phép tìm kiếm theo các tiêu chí sau:
@Tenphim:Tên của bộ phim
@NamsxMin: Từ năm sản xuất
@NamsxMax: Đến năm sản xuất
@Nuocsx: Nước sản xuất
@Theloai: Thể loại phim (hành động/hài/chính kịch…)
Cách làm thứ nhất
CREATE PROCEDURE dbo.TimKiemPhim_1
@Tenphim NVARCHAR(50),
@NamsxMin INT,
@NamsxMax INT,
@Nuocsx NVARCHAR(50),
@Theloai NVARCHAR(50)
AS
SELECT P.*
FROM dbo.Phim P
WHERE (@Tenphim IS NULL OR P.Tenphim like '%'+@Tenphim+'%')
AND (@NamsxMin IS NULL OR P.Namsx >= @NamsxMin)
AND (@NamsxMax IS NULL OR P.Namsx <= @NamsxMax)
AND (@Nuocsx IS NULL OR P.Nuocsx = @Nuocsx)
AND (@Theloai IS NULL OR P.Theloai = @Theloai)
Trong cách làm này ta khai thác trị chân lý của mệnh đề OR – khi tham số @p là NULL, tức là “@p IS NULL” đúng, thì cả mệnh đề ở mỗi dòng AND đúng. Do đó chỉ khi @p được truyền giá trị thì điều kiện tìm kiếm mới được thực hiện. Như vậy code trông đã gọn hơn, mà mở rộng cũng rất dễ dàng, khi cần bổ sung thêm một tham số thì ta chỉ cần viết thêm một dòng lệnh.
Có những trường hợp khi một tham số nào đó được cung cấp ta cần truy nhập vào bảng khác . Giả sử có thêm tham số @TenDienvien để tìm các phim có một diễn viên nào đó tham gia; và giả sử bảng dbo.Dongphim (Đóng phim) chứa tên các diễn viên tham gia đóng phim và quan hệ của bảng dbo.Phim với bảng này là 1-nhiều (mỗi phim có nhiều diễn viên tham gia). Ta có thể thêm đoạn code sau:
AND (@TenDienvien IS NULL OR
EXISTS(SELECT 1 FROM dbo.Dongphim D
WHERE D.PhimID = P.PhimID AND D.TenDienvien like '%'+@TenDienvien+'%')
)
Trong một số tình huống, thủ tục trên có thể chạy rất nhanh ở lần thực hiện đầu nhưng lại chậm hơn nhiều ở lần tiếp theo, khi các tham số tìm kiếm khác với lần đầu. Nguyên nhân của nó là hiện tượng “parameter sniffing” (tôi sẽ nói ở dịp khác). Một cách để khắc phục là thêm lựa chọn “WITH RECOMPILE” vào đoạn khai báo thủ tục, ngay trước từ khóa AS.
Cách làm thứ hai
Dùng sql động, xây dựng chuỗi sql động dựa trên các tham số đầu vào và thực thi chuỗi sql đó.
CREATE PROCEDURE dbo.TimKiemPhim_2
@Tenphim NVARCHAR(50) = NULL,
@NamsxMin INT = NULL,
@NamsxMax INT = NULL,
@Nuocsx NVARCHAR(50) = NULL,
@Theloai NVARCHAR(50) = NULL
AS
DECLARE @SqlStr NVARCHAR(MAX),
@ParamList NVARCHAR(2000)
SELECT @SqlStr = '
SELECT P.*
FROM dbo.Phim P
WHERE (1=1)
'
IF @Tenphim IS NOT NULL
SELECT @SqlStr = @SqlStr + '
AND (P.Tenphim like '''%'+@Tenphim2+'%''')
'
IF @NamsxMin IS NOT NULL
SELECT @SqlStr = @SqlStr + '
AND (P.Namsx >= @NamsxMin2)
'
IF @NamsxMax IS NOT NULL
SELECT @SqlStr = @SqlStr + '
AND (P.Namsx <= @NamsxMax2)
'
IF @Nuocsx IS NOT NULL
SELECT @SqlStr = @SqlStr + '
AND (P.Nuocsx = @Nuocsx2)
'
IF @Theloai IS NOT NULL
SELECT @SqlStr = @SqlStr + '
AND (P.Theloai = @Theloai2)
'
SELECT @Paramlist = '
@Tenphim2 NVARCHAR(50),
@NamsxMin2 INT,
@NamsxMax2 INT,
@Nuocsx2 NVARCHAR(50),
@Theloai2 NVARCHAR(50)
'
EXEC SP_EXECUTESQL @SqlStr,
@Paramlist,
@Tenphim,
@NamsxMin,
@NamsxMax,
@Nuocsx,
@Theloai
Với cách làm này việc viết code có rườm rà và khó theo dõi hơn. Tuy nhiên trong một số trường hợp cách này lại có ưu điểm hơn cách thứ nhất:
1. Thủ tục sp_executesql sẽ lưu kế hoạch thực thi cho mỗi bộ tham số, do đó nó giải quyết vấn đề “parameter sniffing” một cách thông minh hơn so với cách thứ nhất (luôn luôn phải biên dịch lại).
2. Trong trường hợp ta cần SELECT dữ liệu từ các bảng khác nhau tùy theo tham số được truyền. Ví dụ ta có tham số @Phimkinhdien kiểu BIT, khi bằng 1 thì cần SELECT từ bảng dbo.Phimkinhdien, khi bằng 0 thì SELECT từ bảng dbo.Phim như trên. Với cách làm dùng sql động ta có thể dễ dàng làm như sau:
...
SELECT @SqlStr = '
SELECT P.*
FROM ' + CASE WHEN @Phimkinhdien=1 THEN 'dbo.Phimkinhdien' ELSE 'dbo.Phim' END+'
WHERE (1=1)
Với cách làm thứ nhất, ta không có cách nào khác là tạo thêm một nhánh, trong đó lặp lại câu lệnh SELECT và thay bảng dbo.Phim bằng dbo.Phimkinhdien
Bổ sung: một số bạn viết thư hỏi dùng EXEC thay cho sp_executesql có được không. Câu trả lời là bạn nên dùng sp_executesql và tránh EXEC, vì sp_executesql tăng khả năng dùng lại kế hoạch thực thi, trong khi EXEC luôn dẫn đến thủ tục phải biên dịch lại. Một lý do nữa là sp_executesql tránh được lỗi SQL injection, EXEC thì gặp lỗi này. Tôi sẽ trở lại vấn đề so sánh giữa sp_executesql và EXEC trong một dịp khác.
Một bạn nêu trường hợp các cột cần trả về thay đổi tùy theo giá trị của tham số, ví dụ nếu tham số @p=1 thì SELECT các cột col1, col2, col3, còn nếu @p=2 thì SELECT col4, col5, col6. Khi đó cách làm thứ hai ở trên có thể áp dụng dễ dàng, và đây cũng là một trường hợp nó có ưu thế hơn cách làm thứ nhất.
Thanks for watching!