Дополнительные параметры SQL через VB.net

У меня есть страница поиска документов с тремя списками, которые позволяют выбирать несколько вариантов. Они:

Категория А

Год

Категория Б

Только категория A является обязательной, остальные параметры являются необязательными и могут быть пустыми.

Каждый документ может принадлежать к нескольким вариантам в категории A и нескольким вариантам в категории B, но с каждым документом связан только один год.

Я как бы добился этого, создав динамическую строку SQL, но это беспорядочно, и я ненавижу его использовать, поэтому я подумал, что спрошу здесь, может ли кто-нибудь увидеть более простой способ сделать это. Пример динамического SQL-запроса, который я получаю, выглядит следующим образом:

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

Дополнительная информация:

У меня настроены следующие таблицы БД:

Библиотека (которая содержит параметр YEAR)

CategoryADDocs (таблица ссылок, поскольку каждый документ может принадлежать нескольким вариантам в категории A)

CategoryBDocs (таблица ссылок, поскольку каждый документ может принадлежать нескольким вариантам в категории B)

Категория А (список категорий категории А)

Категория B (список категорий категории B)

Я упоминаю «более простой» способ сделать это, в идеале я ищу лучший способ сделать это, было бы круто, если бы это было проще, чем динамическое построение через SQL, но если это более сложно, это не проблема.


sql parameters optional
person Community    schedule 06.04.2010    source источник
comment
есть ли также таблица categoryBdocs?   -  person lexu    schedule 06.04.2010
comment
Извините, да, я просто взял это выше в качестве примера, где таблица categoryBDocs не была запрошена, потому что пользователь не выбрал из нее какие-либо параметры. Но да, вы правы, есть также таблица categoryBDocs.   -  person    schedule 06.04.2010

Ответы (1)


arrow_upward
1
arrow_downward

То, как вы сформулировали запрос в настоящее время, и ввод динамических списков параметров подразумевает, что вы значительно строите запрос в памяти и выполняете его, и в этом случае вам, вероятно, лучше всего использовать клиентский язык, который строит запрос, чтобы просто вставлять элементы, когда они вам нужны. Вы не можете вставить список напрямую через параметр или переменную SQL на любом диалекте, который я знаю, поэтому я бы не стал слишком усердствовать, чтобы избежать того, что вам в любом случае нужно делать.

Тем не менее, если вы хотите использовать более постоянную структуру запроса в том, как вы компилируете это - предположив, что у вас есть таблица CategoryBdocs с полем CategoryBdocID, объявленным как INT Identity (1,1), тогда вы могли бы получить что-то работающее вместе с строки вашего текущего запроса, выполнив

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))

и соедините свой список категории B после -1 - это всегда ничего не вернет, так что это безопасно. Если бы год не входил в список, вы могли бы заставить его работать как необязательный параметр, используя что-то вроде

or Year in (COALESCE(2004, Year))

и когда он выходил с NULL вместо 2004, он совпадал с самим собой и возвращался. Однако вы не можете сделать это со списком, и я не могу придумать чистый способ сделать это в SQL с необязательным списком.

Все это основано на предположении, что вы создаете запрос в памяти и выполняете его, хотя, похоже, это то, что вы делаете. Вы сказали, что вам не нравится это делать, и я согласен - это не идеально эффективно, и передача списка для объединения в запрос делает вас более открытым для SQL-инъекций.

Чтобы обойти это, я бы предложил обернуть это в хранимую процедуру. Если вы затем передадите строки с разделителями, содержащие ваши идентификаторы для каждого из трех списков, вы можете разделить их на таблицы отдельных значений, объединив их с таблицей чисел/подсчета (подробности в другом из моих ответов - SQL выбирает из данных в запросе, где этих данных еще нет в базе данных?). Отсюда вы можете написать это по строкам

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

Я признаю, что это дольше и сложнее, но позволяет вам поместить всю логику в SQL без необходимости построения динамических запросов. Считаете ли вы, что это стоит сложности, я оставляю на ваше усмотрение!

person eftpotrm    schedule 06.04.2010
comment
Отлично, спасибо, что нашли время написать такой подробный ответ, очень признателен. Я думаю, что попробую ваш второй вариант, обернув sproc, он более сложный, но я думаю, что предпочтительнее динамическое построение запроса. - person ; 06.04.2010