Использование произвольного количества параметров в T-SQL

использование такого инструмента, как NHibernate, позволит вам динамически безопасно создавать запросы без необходимости использования хранимых процедур.

У Франса Баумы есть отличная статья о сохраненных процессах по сравнению с динамическим sql и о некоторых преимуществах использования генератора SQL по сравнению с использованием операторов, сгенерированных вручную.

Обычно уловка заключается в том, чтобы просто передать список в виде строки, разделенной запятыми (стиль csv), проанализировать эту строку в цикле и динамически построить запрос.

Вышеупомянутый пост также прав, что, возможно, лучший подход - это не T-SQL, а уровень бизнеса / приложения.

Если вы используете хранимые процедуры, вы можете включить значение по умолчанию для параметров, тогда вы можете выбрать, передавать или не передавать их в клиентском коде, но вам все равно придется объявлять их индивидуально в хранимой процедуре ... Также только если вы при использовании хранимой процедуры вы можете передать один параметр в виде строки значений с разделителями и проанализировать отдельные значения внутри sproc (доступны некоторые "стандартные" функции T-SQL, которые будут разделять записи на динамические табличная переменная для вас)

Если вы используете SQL Server 2008, посмотрите этот художественный передача параметра с табличным значением

Вы также можете рассмотреть возможность полнотекстового поиска и использования CONTAINS или CONTAINSTABLE для более "естественного" поиска.

Может быть излишним для строк размером 1 КБ, но он написан и его нелегко изменить с помощью инъекции.

Каким бы путем вы ни пошли, следите за ограничением параметров SQL Server: ~ 2000 параметров.

Базовое доказательство концепции ... Фактического кода было бы меньше, но поскольку я не знаю ваших имен таблиц / полей, это полный код, поэтому любой может проверить, что он работает, настроить его и т. д.

--Search Parameters

DECLARE @SearchString VARCHAR(MAX)
SET @SearchString='Oakland;City;Planning' --Using your example search
DECLARE @Delim CHAR(1)
SET @Delim=';' --Using your deliminator from the example

--I didn't know your table name, so I'm making it... along with a few extra rows...

DECLARE @Projects TABLE (ProjectID INT, ProjectName VARCHAR(200))
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 1, 'Oakland City Planning'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 2, 'Oakland City Construction'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 3, 'Skunk Works'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 4, 'Oakland Town Hall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 5, 'Oakland Mall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 6, 'StackOverflow Answer Planning'

--*** MAIN PROGRAM CODE STARTS HERE ***

DECLARE @Keywords TABLE (Keyword VARCHAR(MAX))

DECLARE @index int 
SET @index = -1 

--Each keyword gets inserted into the table
--Single keywords are handled, but I did not add code to remove duplicates
--since that affects performance only, not the result.

WHILE (LEN(@SearchString) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@Delim , @SearchString)  
    IF (@index = 0) AND (LEN(@SearchString) > 0)  
      BEGIN   
        INSERT INTO @Keywords VALUES (@SearchString)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Keywords VALUES (LEFT(@SearchString, @index - 1))   
        SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))  
      END  
    ELSE 
      SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index)) 
END


--This way, only a project with all of our keywords will be shown...

SELECT * 
FROM @Projects
WHERE ProjectID NOT IN (SELECT ProjectID FROM @Projects Projects INNER JOIN @Keywords Keywords ON CHARINDEX(Keywords.Keyword,Projects.ProjectName)=0)

Я решил объединить несколько разных ответов в один :-P

Предполагается, что вы передадите список ключевых слов поиска с разделителями (переданный через @SearchString) как VARCHAR (МАКС.), который - реально - вы не столкнетесь с ограничением для поиска по ключевым словам.

Каждое ключевое слово разбито из списка и добавлен в таблицу ключевых слов. Вы, вероятно, захотите добавить код для удаления повторяющихся ключевых слов, но в моем примере это не повредит. Чуть менее эффективен, поскольку в идеале нам нужно оценивать каждое ключевое слово только один раз.

Оттуда любое ключевое слово, не являющееся частью имени проекта, удаляет этот проект из списка ...

Таким образом, поиск по запросу «Окленд» дает 4 результата, а «Окленд; город; планирование» дает только 1 результат.

Вы также можете изменить разделитель, чтобы вместо точки с запятой использовался пробел. Или то, что плавает на вашей лодке

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

Как насчет использования типа данных XML для хранения параметров? Он может быть неограничен и собран во время выполнения ...

Я передаю неизвестное количество PK для обновления таблицы, а затем перекачиваю их во временную таблицу. Затем легко обновить PK в PKTempTable.

Вот код для анализа типа данных XML ...

    INSERT INTO #ERXMLRead (ExpenseReportID)
    SELECT ParamValues.ID.value('.','VARCHAR(20)')
    FROM @ExpenseReportIDs.nodes('/Root/ExpenseReportID') as ParamValues(ID)

Как и в некоторых других ответах, вы можете разобрать строку с разделителями или XML-документ. См. эта отличная ссылка, который демонстрирует оба метода с SQL Server.