Sql filter with optional parameter - Bộ lọc tham số tùy chọn

Nếu như bạn muốn tạo bộ lọc với nhiều tham số (có 1 hoặc nhiều trong số chúng là tham số không bắt buộc). Rõ ràng rằng có thể bạn nghĩ ngay tạo câu truy vấn sử dụng mệnh đề if then ngay trong mệnh điều kiện where. Chẳng hạn giống 1 ví dụ sau, và thực tế nó không hoạt động:

SELECT tblVillas.*, tblWeeklyPrices.price FROM tblVillas

INNER JOIN tblWeeklyPrices ON tblVillas.villaId = tblWeeklyPrices.villaFK

WHERE

IF @accomodationFK <> NULL THEN

     accomodationTypeFK = @accomodationFK

IF @regionFK <> NULL THEN

     AND regionFK = @regionFK

IF @sleeps <> NULL THEN

     AND sleeps = @sleeps

IF @priceFloor <> NULL THEN

     AND price >= @priceFloor AND price <= @priceCeil

END

Bách đơn giản hơn, hãy tạo câu truy vấn tương tự sau đây:

SELECT tblVillas.*, tblWeeklyPrices.price

FROM tblVillas

INNER JOIN tblWeeklyPrices

ON tblVillas.villaId = tblWeeklyPrices.villaFK

WHERE

  (@accomodationFK IS NULL OR accomodationTypeFK = @accomodationFK)

  AND (@regionFK IS NULL OR regionFK = @regionFK)

  AND (@sleeps IS NULL OR sleeps = @sleeps)

  AND (@priceFloor IS NULL OR (price BETWEEN @priceFloor AND @priceCeil))

Ý nghĩa của việc làm này là ta đã tạo ra một giá trị mặc định NULL cho tham số tham gia vào quá trình lọc. Nếu 1 tham số của bộ lọc nào đó khi truyền vào Proc mang giá trị mặc định NULL thì mặc nhiên nó làm cho trường dữ liệu (cột) thỏa mãn điều kiện và bản ghi đó sẽ được chọn lọc. Bạn hãy phát triển đoạn mã đối với kiểu dữ liệu cấu trúc không thể mang giá trị NULL nhé.
Một ví dụ hoàn chỉnh tạo một bộ lọc như sau:

CREATE PROCEDURE pr_student_filter

(

    @Id VARCHAR(50) = NULL,

    @Name VARCHAR(50) = NULL,

    @Email VARCHAR(50) = NULL

)

AS

SELECT *

FROM Student s

WHERE

    ((@Id IS NULL) OR (s.ID = @Id))

    AND ((@Name IS NULL) OR (s.Name = @Name))

    AND ((@Email IS NULL) OR (s.Email = @Email))

-- Select tất cả dữ liệu, không lọc

EXEC pr_student_filter

-- Câu lệnh trên tương đương với

EXEC pr_student_filter NULL, NULL, NULL

-- Lọc sinh viên có mã hiệu là '20073522'

EXEC pr_student_filter '20073522', NULL, NULL

-- Lọc sinh viên có tên là 'Nhu Bao Vu'

EXEC pr_student_filter NULL, 'Nhu Bao Vu', NULL

-- Lọc sinh viên có email là 'vunb@bkindex.com'

EXEC pr_student_filter NULL, NULL, 'vunb@bkindex.com'

-- Lọc sinh viên có tên là 'Bao Vu' và email là 'vunb@bkindex.com'

         EXEC pr_student_filter NULL, 'Bao Vu', 'vunb@bkindex.com'

Một số biến thể khác cho mệnh đề điều kiện WHERE có thể sử dụng:

WHERE col1 =  ISNULL(@Param1, col1)
WHERE @Param1 IN (Col1,  NULL)