Tìm kiếm Unicode sử dụng SQL Động

1. Table

CREATE TABLE [dbo].[DMTinhThanh]
(
[TinhThanhID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
[MaTinhThanh] NVARCHAR(4) NOT NULL,
[TenTinhThanh] NVARCHAR(255) NOT NULL
)

2. Tìm kiếm

– Nếu dùng cộng string trực tiếp thì không ăn phải qua một biến trung gian truyền vào trong câu lệnh statement trong  sp_executesql
DECLARE @sql NVARCHAR(MAX);
DECLARE @param NVARCHAR(100)
DECLARE @ParameDefine NVARCHAR(MAX);
SET @ParameDefine = ‘@param2 NVARCHAR(100)’

SET @param=N’%Bắc%’;
SET @sql =’SELECT TOP 100 * FROM dbo.DMTinhThanh WHERE TenTinhThanh LIKE @param2′
EXEC sp_executesql @sql,@ParameDefine,@param2 = @param

3. Tham khảo thêm tại

http://sqlwithmanoj.wordpress.com/tag/dynamic-sql/

Advertisements

Bus_SP_T_Arrive_Leave_Rotate_GetAllLostCalendar

CREATE PROCEDURE [Bus_SP_T_Arrive_Leave_Rotate_GetAllLostCalendar]
	@DateActive DATETIME,
	@FK_IDRoute INT,
	@FK_CompanyID INT,
	@NoteID VARCHAR(16)
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @SqlMain			NVARCHAR(MAX)
	DECLARE @SqlCalendar		        NVARCHAR(MAX)
	DECLARE @SqlArrive			NVARCHAR(MAX)
	DECLARE @SqlArriveRotate	        NVARCHAR(MAX)
	DECLARE @SqlWhere			NVARCHAR(MAX)
	DECLARE @SqlXe				NVARCHAR(MAX)
	DECLARE	@ParamsDefine		        NVARCHAR(500)
	
	SET  @ParamsDefine ='@DateActive2 DATETIME, @FK_IDRoute2 INT, @FK_CompanyID2 INT, @NoteID2 VARCHAR(16)' 
	
	SET @SqlMain = '
				SELECT 
					a.DateActive,
					a.FK_CompanyID,
					a.FK_IDRoute,
					a.NoteID,
					a.CalendarID,
					a.FK_VehicleID,
					b.BienSoXe,
					a.Direction,
					a.Flight,
					a.TimeStart,
					a.TimeEnd,
					a.RotateID,
					a.TimeStartTT,
					a.TimeEndTT,
					a.DaDuyet
			FROM '
	SET @SqlMain = @SqlMain + 
			'(
			SELECT 
				   calendar.ID AS CalendarID,
				   calendar.NoteID,
				   calendar.DateActive,
				   calendar.FK_VehicleID,
				   calendar.FK_CompanyID,
				   calendar.FK_IDRoute,
				   calendar.TimeStart,
				   calendar.TimeEnd,
				   calendar.Direction,
				   calendar.Flight,
				   Rotate.RotateID,
				   Rotate.TimeStartTT,
				   Rotate.TimeEndTT ,
				   Rotate.DaDuyet
			FROM'
	SET @SqlCalendar ='( SELECT 
						ID , 
						NoteID,
						DateActive,
						FK_VehicleID,
						FK_CompanyID,
						FK_IDRoute,
						Direction,
						Flight,
						TimeStart,
						TimeEnd
					 FROM dbo.Bus_T_CalendarDailyOfCompany
					 WHERE	FK_CompanyID = @FK_CompanyID2
							AND FK_IDRoute = @FK_IDRoute2
							AND DateActive = @DateActive2)AS calendar 
					 LEFT JOIN 
					 '
	SET @SqlArrive = '(
					SELECT 
						CalendarID,
						TimeStartQD,
						TimeEndQD
					FROM dbo.Bus_T_Arrive_Leave
					WHERE DateActive = @DateActive2 ) AS Arrive
					ON calendar.ID = Arrive.CalendarID
					LEFT JOIN '
	SET @SqlArriveRotate = '(	
						SELECT 
							CalendarID AS RotateID,
							NoteID,
							TimeStartQD,
							TimeEndQD,
							TimeStartTT,
							TimeEndTT,
							1 AS DaDuyet
						FROM dbo.Bus_T_Arrive_Leave_Rotate 
						WHERE DateActive = @DateActive2
					) AS Rotate
						ON calendar.NoteID = Rotate.NoteID 
						AND calendar.TimeStart = Rotate.TimeStartQD
						AND calendar.TimeEnd = Rotate.TimeEndQD'
	
	
	SET @SqlWhere = ' WHERE  Arrive.CalendarID IS NULL '
	--SET @SqlWhere =  @SqlWhere + ' AND Rotate.TimeStartQD IS NULL '
	
	IF(LEN(@NoteID)>0)
	BEGIN
		SET @SqlWhere =  @SqlWhere + ' AND Calendar.NoteID = @NoteID2'
	END
	SET @SqlWhere = @SqlWhere + ' ) AS a '
	
	
	SET @SqlXe = 'LEFT JOIN 
				  dbo.T_MACUNGXINGHIEP_XE AS b
				  ON  a.FK_VehicleID = b.IDBKS'
	
	SET @SqlMain = 
					 @SqlMain 
					+ @SqlCalendar
					+ @SqlArrive
					+ @SqlArriveRotate
					+ @SqlWhere
					+ @SqlXe
  --print @SqlMain		 
  EXEC sp_executesql @SqlMain,
				     @ParamsDefine,
				     @DateActive2 = @DateActive,
					 @FK_IDRoute2 = @FK_IDRoute,
					 @FK_CompanyID2 =@FK_CompanyID,
					 @NoteID2 =@NoteID
END

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.

http://www.sqlviet.com/blog/tim-kiem-voi-nhieu-tham-so