[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.

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!

laptrinhvb via sqlviet.com