SQL optional parameters through VB.net

Question

I've a document search page with three listboxes that allow multiple selections. They're:

Category A

Year

Category B

Only category A is mandatory, the others are optional parameters and might be empty.

Each document can belong to multiple options in Category A and multiple options Category B but each document only has one year associated with it.

I've kind of got this working through building up a dynamic SQL string but it's messy and I hate using it so I thought I'd ask here if anyone could see an easier way of doing this. An example of the kind of dynamic SQL query i end up with follows:

 select * 
from library
where libraryID in
(select distinct libraryID from categoryAdocs where categoryAdocID in (4))
or year in (2004)

Additional Details:

I have the following DB tables set up:

Library (which contains the YEAR parameter)

CategoryADocs (link table because each doc can belong to multiple options in categoryA)

CategoryBDocs (link table because each doc can belong to multiple options in categoryB)

CategoryA (list of Cat A categories)

CategoryB (list of Cat B categories)

I mention an 'easier' way of doing this, ideally I'm looking for the best way of doing this, it would be cool if it was easier than dynamically buidling it up through SQL but if it's more involved that's no problem.

Answer 1

The way you've phrased the query at present and the inputting of dynamic lists of parameters implies to me that you're building the query significantly in memory and running it, in which case you're probably best off using the client language that builds the query to just drop in the elements when you want them anyway. You can't insert a list directly via an SQL parameter or variable in any dialect I know, so I wouldn't bother going to great lengths to avoid something you largely need to do anyway.

That said, if you wanted to use a more constant query structure in how you're compiling this - Assuming you've got a CategoryBdocs table with a CategoryBdocID field declared as INT Identity(1,1) then you could get something working along the lines of your current query by doing

select * 
from library
where libraryID in
    (select distinct libraryID from categoryAdocs where categoryAdocID in (4))
or LibraryID in
    (select distinct libraryID from categoryBdocs where categoryBdocID in (-1))

and concatenate your category B list after the -1 - that will always return nothing so that's safe. If Year wasn't coming in as a list you could get that working as an optional parameter using something like

or Year in (COALESCE(2004, Year))

and when it came out with NULL instead of 2004 it'd match with itself and return. However, you can't do that with a list and I can't think of a clean way to do it in SQL with an optional list.

This is all based on the assumption you're building the query in memory and executing it though as that's what you seem to be doing. You've said you don't like doing this and I agree - it's not ideally efficient and passing in a list to concatenate into a query leaves you open to SQL Injection more easily.

To get round this, I'd suggest wrapping this in a stored proc. If you then pass in delimited strings containing your IDs for each of the three lists you can split them into tables of individual values by joining against a numbers / tally table (Details in another of my answers - http://stackoverflow.com/questions/2580812/sql-select-from-data-in-query-where-this-data-is-not-already-in-the-database/2583802#2583802). From here you can write it along the lines of

SELECT L.* 

FROM Library L
    INNER JOIN CategoryADocs A
        ON L.LibraryID=A.LibraryID

    INNER JOIN ([CategoryA Derived table]) ADocs 
        ON A.CategoryAdocID=ADocs.CategoryADocID

    LEFT JOIN (SELECT B.* FROM CategoryBDocs B
                INNER JOIN [CategoryB Derived Table] BDocs
                    ON B.CategoryBdocID=BDocs.CategoryBDocID) B
        ON L.LibraryID=B.LibraryID

    LEFT JOIN ([Years Derived table]) Y
        ON L.Year=Y.Year

WHERE
    COALESCE(B.LibraryID,-1)=CASE WHEN Len(@BIDs) > 1 THEN B.LibraryID ELSE -1
    AND COALESCE(L.Year,-1)=CASE WHEN Len(@Years) > 1 THEN L.Year ELSE -1

Which I will admit is longer and more complex but allows you to put all the logic into the SQL without needing any dynamic query building. Whether you think that's worth the complexity I'll leave up to you!