Transact-SQL – Input Search in a Filter selection
This is some basic thing, but I think it will be cool to blog about.
Assuming you has this scenario:
Search Criteria | : | [DropDownList] Item[0] – New IC Item[1] – Old IC Item[2] – Passport |
Search Now | : | [TextBox] |
Normally what people will normally do is:
a) Have 3 stored procedures cater for each selection
To further illustrate this and assume there is no layers as it is easier to explain:
I have a dropdownlist SelectedIndexChanged event
If (ddlCriteria.SelectedValue == “001”)
{
// Call the 1st stored proc
}
Else if (ddlCriteria.SelectedValue == “002”)
{
// Call the 2nd stored proc
}
Else if (ddlCriteria.SelectedValue == “003”)
{
// Call the 3rd stored proc
}
*You can use SelectedItem or pass the index too. Up to your coding style.
In each stored procedure, basically they will do this:
Stored Proc 1
SELECT * FROM Customers WHERE NewIC = @input
Stored Proc 2
SELECT * FROM Customers WHERE OldIC = @input
Stored Proc 3
SELECT * FROM Customers WHERE Passport = @input
*input is the search textbox value
The result is returned and bind into a GridView or DataGrid.
b) There is another method which most developers will use. Dynamic SQL. Basically most people will use this way and be satisfied about it as it is using 1 stored procedure. However it is a bad practice to use Dynamic SQL. Try to avoid as many as possible.
Stored Proc 1
DECLARE @FilterKey CHAR(1);
DECLARE @SearchText VARCHAR(50);
DECLARE @ColumnName VARCHAR(10);
DECLARE @dSql VARCHAR(MAX);
IF @FilterKey = ‘N’
BEGIN
SET @dSql = ‘SELECT * FROM Customers WHERE NewIC = ‘’’ + @SearchText + ‘’’ ORDER BY [Name] ASC’
END
IF @FilterKey = ‘O’
BEGIN
SET @dSql = ‘SELECT * FROM Customers WHERE OldIC = ‘’’ + @SearchText + ‘’’ ORDER BY [Name] ASC’
END
IF @FilterKey = ‘P’
BEGIN
SET @dSql = ‘SELECT * FROM Customers WHERE Passport = ‘’’ + @SearchText + ‘’’ ORDER BY [Name] ASC’
END
EXEC SP_EXECUTESQL @dSql
*You can be creative in your Dynamic SQL or introducing Case statement.
So what are the better ways to achieve this kind of search?
Basically I will recommend the 2nd way as it is more efficient.
a) Use COALESCE
select * from Customers WHERE
NewIC = COALESCE(@NewIC, NewIC) AND
OldIC = COALESCE(@OldIC, OldIC) AND
Passport = COALESCE(@Passport, Passport)
b) Use IS NULL and IN
select * from Customers
WHERE ((@NewIC IS NULL) OR (NewIC IN (SELECT NewIC FROM Customers st WHERE NewIC = @NewIC))) AND
((@OldIC IS NULL) OR (OldIC IN (SELECT OldIC FROM Customers st WHERE OldIC = @OldIC))) AND
((@Passport IS NULL) OR (Passport IN (SELECT Passport FROM Customers st WHERE Passport = @Passport)))
It is necessary to have the IS NULL checking. Basically if you do not enter any search key, it will still produce you a result.
However if you decide that if user does not input any data in the search text box and you do not want to return any results.
select * from Customers
WHERE ((NewIC IN (SELECT NewIC FROM Customers st WHERE NewIC = @NewIC))) OR
((OldIC IN (SELECT OldIC FROM Customers st WHERE OldIC = @OldIC))) OR
((Passport IN (SELECT Passport FROM Customers st WHERE Passport = @Passport)))
With this, as long no values entered in the search text box, it will not produce anything.
How about if you want to use the LIKE keyword here? It can be done easily.
select * from Customers
WHERE ((@NewIC IS NULL) OR (NewIC IN (SELECT NewIC FROM Customers st WHERE NewIC LIKE @NewIC + '%'))) AND
((@OldIC IS NULL) OR (OldIC IN (SELECT OldIC FROM Customers st WHERE OldIC LIKE @OldIC + '%'))) AND
((@Passport IS NULL) OR (Passport IN (SELECT Passport FROM Customers st WHERE Passport LIKE @Passport + '%')))
Okay why the 2nd way is better as the 1st way is so much code cleaner?
Basically when you perform coalesce it will do a table scan on the particular table you are trying to query at.
Hope you find it useful. Thanks.