T-SQL WHERE statement in function of parameter

Question

I have the following table:

TABLE1
| ID | NAME   | TYPE  | COLOR  |
|  1 | APPLE  | FRUIT | RED    |
|  2 | BANANA | FRUIT | YELLOW |
|  3 | LEMON  | FRUIT | YELLOW |
|  4 | TOMATO | VEGET | RED    |
|  5 | PEPPER | VEGET | RED    |
|  6 | PEAR   | FRUIT | GREEN  |

And the input fields @TYPE and @COLOR. If @TYPE = '*' and @COLOR = '*' then:

SELECT *
FROM TABLE1

otherwise, if @TYPE = 'VEGET' and @COLOR = '*':

SELECT *
FROM TABLE1
WHERE TYPE = 'VEGET'

if @TYPE = '*' and @COLOR = 'YELLOW':

SELECT *
FROM TABLE1
WHERE COLOR = 'YELLOW'

and the last case, if @TYPE = 'VEGET' and @COLOR = 'YELLOW':

SELECT *
FROM TABLE1
WHERE TYPE = 'VEGET'
AND COLOR = 'YELLOW'

Obviously it is not just two fields or parameters...
How can I write an efficient query to check all cases?

Jonny · Accepted Answer · 2017-05-03 11:19:10Z

1

SELECT *
FROM TABLE1
WHERE 
   (TYPE = @TYPE OR @TYPE='*')
AND
   (COLOR = @COLOR OR @COLOR='*')

answered May 3, 2017 at 11:19

Jonny

1,21110 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gotqn · Accepted Answer · 2017-05-03 13:53:09Z

If you are filtering by many values, you can build dynamic T-SQL statement. For example:

First declare the basic statement:

DECLARE @DynamicSQLStatement NVARCHAR(MAX);

SET @DynamicSQLStatement = 'SELECT * FROM TABLE1'

Then check if a filtering is applied (if yes, add WHERE):

IF @Type <> '*' OR <> @Color <> '*' OR ...
BEGIN;
    SET @DynamicSQLStatement =  @DynamicSQLStatement + ' WHERE 1=1 ';
END;

Then add check for each parameter:

IF @Type <> '*' 
BEGIN;
    SET @DynamicSQLStatement =  @DynamicSQLStatement + ' AND @Type = ' + @Type; 
END;

IF @Color <> '*' 
BEGIN;
    SET @DynamicSQLStatement =  @DynamicSQLStatement + ' AND @Color = ' + @Color; 
END;

Finally, execute the statement:

EXEC sp_executesql @DynamicSQLStatement;

It's nice because in the final statement only the filtering that is specified is included. In some complicated filtering scenarios this can improve performance.

Few important notes:

you are not allowed to use this in function, only stored procedure (which is limitation);
you need to escape single quotes in strings (using double single quotes)
if your parameter value is a number, you need to cast it to string

Collectives™ on Stack Overflow

T-SQL WHERE statement in function of parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related