Thứ Năm, ngày 08 tháng 12 năm 2011

// // Leave a Comment

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

0 comments: