How to come up one SQL based on three db tables?

SQL Server Administration
1

Hello Everyone,

How to come up one SQL for MS SQL 2019 Server for searching three categories such as
Price Range, Brand, and CPU db tables.

For examples:

Price Range (db table)
$100 - $300
$300 - $400
$400 - $500
$500 - $600

Brand (db table)
DELL
Acer
ASUS

CPU (db table)
Intel core 2
Intel core 3

Now if user checks the DELL checkbox only. Then SQL return all
data that match DELL in the Brand db table.

If user checks the DELL and ASUS checkboxes only. Then SQL return
all data that match DELL and ASUS in the Brand db.

If user checks the DELL and $300 - $400 only. Then SQL return
all data that match DELL and $300 - $400 in the Brand & Price Range db tables.

If user checks the $400 - $500 checkbox only. Then SQL return
all data that match $400 - $500 in the Price Range db table.

If user checks the $500 - $600 and Acer and Intel core 2 and Intel core 3.
Then SQL return data between $500 and $600 and Brand = Acer and CPU = Intel core 2
and CPU = Intel core 3 in the Price Range db table, Brand db table, and CPU db table.

... and so on.

How to come up such SQL for MS SQL 2019 Server?

Thanks a lot in advance!

Amy Wong

2

I would use Dynamic SQL in this case. You create a stored procedure like SearchProducts and every search item as a parameter.

You can create a sql based on that parameters and you keep things simple to maintain. However, you should be aware of the security issues involved with Dynamic SQL. You don't want someone be able to create a sql statement like SELECT * FROM users or DROP DATABASE.

Using Dynamic SQL in Stored Procedures - SQLTeam.com

3

To continue with @RogierPronk 's suggestion, you should consider reading this as well:

It's a good technique for the kind of multiple search/predicate feature you're trying to support.

1 Like
6

RogierPronk, thanks a lot for your help!

7

robert_volk, thanks a lot for your help!