Is there a way to create a parameterized query or stored procedure that accepts <= N parameters?

Question

Suppose I have a search screen that is intended for looking up items. There are various optional search options on the screen that will cause the SQL query statement to vary.

Here are some example searches:

1. Description search
2. Description search + item supplier id
3. Description search + item supplier id + item hierarchy level 1 id
4. Description search + item supplier id + item hierarchy level 1 id + level 2 id
5. item hierarchy level 1 id + level 2 id (no description, no item supplier id)

...you get the idea. There are quite a number of possible combinations. I was hoping to use parameterized queries for the performance benefits and such (plus I'm using them for the rest of the queries throughout the program).

Is there a way to do this or am I forced to either create each possible query and matching SQLiteCommand object or build the query string dynamically with a StringBuilder based on the options selected?

I'm using using the SQLite.NET data provider with C# 3.0 (on the 3.5 compact framework).

---

UPDATE

Based on some of the suggestions with null default values for the parameters and using (@param isnull or column = @param), I think I should be able to get this to work. I'll keep you posted.

NOTE: I am avoiding using stored procedures because the rest of the code uses parameterized queries instead of stored procedures. I'd like to keep everything consistent for the sanity of future maintenance programmers. It shouldn't make too much of a difference anyway.

---

UPDATE 2

This worked great on a desktop system (which is where I did my initial testing for the queries). However, it was very slow on the Windows CE Device I was using. Unusably slow. All the same, I can definitely use this in the future and it's very handy. Just not when running queries on a mobile device.

Thanks

Answer 1

Your probably best off creating stored procedures for each case.

If you have inline SQL code in your c# built with a StringBuilder then the execution plans will never be cached and it will not perform as well as it would with stored procedures.

Answer 2

You can assign the parameters default values and handle the logic within your stored procedure:

create procedure mySproc(@description nvarchar(20) = null, @supplierid int = null, etc...)

handle the logic of whether the parameters are null in the body of the sproc.

Answer 3

From the stored procedure side you can default values to null then build your where clause to accommodate this null value.

ALTER Procedure FooProcedure 
    @SupplierID INT = NULL,
    @LevelOne INT = NULL 
AS
BEGIN

    SELECT SupplierID, LevelOne 
      FROM FooTable
     WHERE @SupplierID IS NULL OR SupplierID = @SupplierID
       AND @LevelOne IS NULL OR LevelOne = @LevelOne 

END