CODEWITHSUNDEEP
sqlsql-servert-sql
Developer
Developer

Reputation: 245

complex filter conditions with SQL query

The application passes 5 different filter conditions from the UI to the query. ie,--STORE CODE DESC NOTES QTY.

When I add these conditions with different possibilities it is coming very long, ie 

--1 0   0   0   0
IF @Store<>'0' AND @code='' AND @DESC='' AND @Notes='' AND @QTY=0 
--1 1   0   0   0
--1 1   0   0   1
--1 1   1   0   0
--1 1   1   1   0
etc..........

is there any way to simplify this to pass as single query. Hope the question is understandable.

Sample code I have done as below,

SET @sql = 'Select * from tbl_store Where Inactive=0 ';
--10000
    IF @Store<>'0' AND @Code='' AND @Description='' AND @Notes='' --AND @Qty<>''
    SET @sql += ' AND Store=@Store  AND Quantity = @Qty';

    --11000
    ELSE IF @Store<>'0' AND @Code<>'' AND @Description='' AND @Notes='' --AND @Qty<>''
    SET @sql += ' AND Store=@Store  AND Code=@Code  AND Quantity = @Qty';

........................

Upvotes: 1

Views: 1529

Answers (3)

gmiley
gmiley

Reputation: 6604

I would do away with the dynamic query if possible and do something like this:

select * 
from tbl_store ts
where ts.Inactive = 0
and (
    ( @Store <> '0' and @Description = '' and @Notes = '' and Store = @Store and Quantity = @Qty)
or
    (@Store <> '0' and @Code <> '' and @Notes <> '' and Code = @Code and Store = @Store and Quantity = @Qty)
);

Using dynamic queries, such as yours, can lead to security holes, and general confusion about how things are being done. In my opinion, it should be one of last resorts.

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15175

I would place any validation outside of the query and simply filter your query as follows.

SET @IsValidFilter=<YOUR VALIDATION LOGIC HERE>--IF YOU CAN'T TRUST INCOMING VALUES

SELECT
    *
FROM
    MyTable
WHERE
    (@IsValidFilter=1)
    AND 
    (@Store IS NULL OR MyTable.StoreID=@Store) 
    AND 
    (@code= IS NULL OR MyTable.CodeID=@Code)
    AND
    (@DESC IS NULL OR MyTable.Description=@Desc)
    AND
    (@Notes IS NULL OR MyTable.Notes=@Notes)

If you can't trust the values being passed in and need some logic based on a combination of parameters values then it would be more readable to create a @ValidFilter flag and simply add a final AND @ValidFilter=1 and not do too much in the WHERE.

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Do them one at a time:

SET @sql = 'Select * from tbl_store Where Inactive = 0 ';
IF @Store <> '0' 
    SET @sql += ' and Store = @Store';
IF @Qty <> ''
    SET @sql += ' and Quantity = @Qty';
. . . .

For performance reasons, what you are doing is a good idea. If an appropriate index is available, the ultimate where clause should be able to take advantage of appropriate indexes. A single where condition such as this won't:

where (@store = '0' or start = @store) and
      (@qty = '' or quantity = @qty) and
      . . .

Upvotes: 1

Related Questions