Query with different WHERE clauses

[Cralis]

Hi guys.

I have a database of R/C Helicopters.

The user wants to filte down on a specific helicopter configuration.
So the GUI has a screen with 5 dropdownlists.

ESC (Electric Speed Controller), Motor, Gyro, Engine and Servo.

Lets say the user wants to find all helicopters using a Z80 motor. So,
he selects that from a drop down, and the rest remain as 'Select
one..'.

So I pass paramteres to my stored procedure....

sp_find_aircraft esc=0, motor=67, gyro=0, engine=0, servo=0
(67 being the ID of the component he wants to look up.. in this case,
the Z80 engine).

How do I create the where clause for this? My select looks like this:

SELECT ua.id, c.[Model]
FROM dbo.[UserAircraft] ua
LEFT JOIN dbo.[UserAircraftComponent] uac1 ON uac1.useraircraftid =
ua.id
INNER JOIN dbo.[Component] c ON c.id = uac1.componentid

I have no where clause yet, as I'm not sure how to handle dffering
selections.

In the above example, my where clause would simply be:

WHERE c.id = 67

And that would return me a correct recordset of all UserAircraft
having a Z80 engine.

But now the user wants a result with the Z80, as well as maybe a GY401
gyro... so now I pass:
sp_find_aircraft esc=0, motor=67, gyro=145, engine=0, servo=0

I kinda need a dynamic where clause....
Is this possible?

A kind of:
IF @gyro 0
add 'OR Componentid=145

type of thing.

[Erland Sommarskog]

Cralis (ad***@myschoolmates.com) writes:

Lets say the user wants to find all helicopters using a Z80 motor. So,
he selects that from a drop down, and the rest remain as 'Select
one..'.

So I pass paramteres to my stored procedure....

sp_find_aircraft esc=0, motor=67, gyro=0, engine=0, servo=0
(67 being the ID of the component he wants to look up.. in this case,
the Z80 engine).

How do I create the where clause for this? My select looks like this:

There are a couple of options, and I have an article (in two versions) on
my web site that discusses this in detail:
http://www.sommarskog.se/dyn-search.html.

By the way, do not use the sp_ prefix for your stored procedures. This
prefix is reserved for system procedures.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cralis]

Thanks Erland!
Agh! Forgot about the poor use of 'sp_'.
Foolish - will resolve, thanks.