Хранимая процедура T-SQL, которая принимает несколько значений Id

Существует ли изящный способ обработки списка идентификаторов в качестве параметра хранимой процедуры?

Например, я хочу, чтобы департаменты 1, 2, 5, 7, 20 были возвращены моей хранимой процедурой. Раньше я перешел в список идентификаторов с разделителями-запятыми, например, код ниже, но чувствую себя действительно грязным.

SQL Server 2005 - мое единственное применимое ограничение, которое я думаю.

create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)     
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)

Ответы

Ответ 1

Эрланд Соммарског сохранил авторитетный ответ на этот вопрос за последние 16 лет: Массивы и списки в SQL Server.

Существует не менее дюжины способов передать массив или список в запрос; каждый из них имеет свои собственные плюсы и минусы.

Табличные параметры. SQL Server 2008 и выше и, вероятно, ближе всего к универсальному "лучшему" подходу.
Итерационный метод. Передайте строку с разделителями и пропустите ее.
Использование среды CLR. SQL Server 2005 и выше только на языках .NET.
XML. Очень хорошо для вставки многих строк; может быть излишним для SELECT.
Таблица номеров. Более высокая производительность/сложность, чем простой итерационный метод.
Элементы фиксированной длины. Фиксированная длина улучшает скорость по разделительной строке
Функция номеров. Вариации таблицы чисел и фиксированной длины, где число генерируется в функции, а не принято из таблицы.
Рекурсивное общее выражение таблицы (CTE). SQL Server 2005 и выше, все еще не слишком сложная и более высокая производительность, чем итеративный метод.
Динамический SQL. Может быть медленным и имеет последствия для безопасности.
Передача списка Множество параметров. Довольно утомительный и простой, но простой.
Действительно медленные методы. Методы, которые используют charindex, patindex или LIKE.

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

Ответ 2

Да, ваше текущее решение подвержено атакам SQL-инъекций.

Лучшим решением, которое я нашел, является использование функции, которая разбивает текст на слова (здесь есть несколько статей, или вы можете использовать этот из моего блога), а затем присоедините это к своей таблице. Что-то вроде:

SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId

Ответ 3

Вы можете использовать XML.

например.

declare @xmlstring as  varchar(100) 
set @xmlstring = '<args><arg value="42" /><arg2>-1</arg2></args>' 

declare @docid int 

exec sp_xml_preparedocument @docid output, @xmlstring

select  [id],parentid,nodetype,localname,[text]
from    openxml(@docid, '/args', 1)

Создана команда sp_xml_preparedocument.

Это даст результат:

id  parentid    nodetype    localname   text
0   NULL        1           args        NULL
2   0           1           arg         NULL
3   2           2           value       NULL
5   3           3           #text       42
4   0           1           arg2        NULL
6   4           3           #text       -1

у которого есть все (больше?) того, что вам нужно.

Ответ 4

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

Таким образом, вы только разобираете один раз.

Проще всего использовать один из "Сплит" UDF, но так много людей разместили примеры из них, я решил, что пойдет по другому пути;)

В этом примере будет создана временная таблица для подключения (#tmpDept) и заполните ее идентификатором отдела, в который вы прошли. Я предполагаю, что вы разделяете их запятыми, но вы можете - конечно - измените его на все, что хотите.

IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL
BEGIN
    DROP TABLE #tmpDept
END

SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','')

CREATE TABLE #tmpDept (DeptID INT)
DECLARE @DeptID INT
IF IsNumeric(@DepartmentIDs)=1
BEGIN
    SET @[email protected]
    INSERT INTO #tmpDept (DeptID) SELECT @DeptID
END
ELSE
BEGIN
        WHILE CHARINDEX(',',@DepartmentIDs)>0
        BEGIN
            SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1)
            SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs))
            INSERT INTO #tmpDept (DeptID) SELECT @DeptID
        END
END

Это позволит вам передать один идентификатор отдела, несколько идентификаторов с запятыми между ними или даже несколько идентификаторов с запятыми и пробелами между ними.

Итак, если вы сделали что-то вроде:

SELECT Dept.Name 
FROM Departments 
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name

Вы увидите имена всех идентификаторов отдела, которые вы передали в...

Опять же, это можно упростить, используя функцию для заполнения временной таблицы... Я в основном делал это без одного, чтобы убить некоторую скуку: -P

- Кевин Фэрчайлд

Ответ 5

Супербыстрый XML-метод, если вы хотите использовать хранимую процедуру и передать список разделов с разделителями-запятыми:

Declare @XMLList xml
SET @XMLList=cast('<i>'+replace(@DepartmentIDs,',','</i><i>')+'</i>' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))

Весь кредит принадлежит Guru Блог Брэда Шульца

Ответ 6

Попробуйте следующее:

@list_of_params varchar(20) -- value 1, 2, 5, 7, 20 

SELECT d.[Name]
FROM Department d
where @list_of_params like ('%'+ CONVERT(VARCHAR(10),d.Id)  +'%')

очень просто.