Procedura składowana T-SQL, która akceptuje wiele wartości identyfikatorów

Czy istnieje wdzięczny sposób obsługi przekazywania listy identyfikatorów jako parametru do procedury składowanej?

Na przykład chcę, aby działy 1, 2, 5, 7, 20 były zwracane przez moją procedurę składowaną. W przeszłości przekazywałem listę identyfikatorów oddzielonych przecinkami, tak jak w poniższym kodzie, ale czuję się naprawdę brudny, robiąc to.

Myślę, że SQL Server 2005 jest moim jedynym ograniczeniem.

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

Erland Sommarskog od 16 lat utrzymuje autorytatywną odpowiedź na to pytanie: tablice i listy w SQL Server .

Istnieje co najmniej tuzin sposobów przekazywania tablicy lub listy do zapytania; każdy ma swoje unikalne zalety i wady.

• Parametry wartościowane w tabeli . Tylko SQL Server 2008 i nowsze wersje i prawdopodobnie najbliższe uniwersalnemu podejściu „najlepszego”.
• Metoda iteracyjna . Przekaż rozdzielony ciąg i zapętl go.
• Korzystanie z CLR . SQL Server 2005 i nowsze wersje tylko z języków .NET.
• XML . Bardzo dobre do wstawiania wielu wierszy; może być przesadą dla SELECT.
• Tabela liczb . Wyższa wydajność / złożoność niż prosta metoda iteracyjna.
• Elementy o stałej długości . Stała długość poprawia prędkość nad ograniczonym łańcuchem
• Funkcja liczb . Odmiany tabeli liczb i stałej długości, w których liczba jest generowana w funkcji, a nie pobierana z tabeli.
• Recursive Common Table Expression (CTE). SQL Server 2005 i nowsze, wciąż niezbyt złożone i wydajniejsze niż metoda iteracyjna.
• Dynamiczny SQL . Może być powolny i mieć wpływ na bezpieczeństwo.
• Przekazywanie listy jako wielu parametrów . Żmudne i podatne na błędy, ale proste.
• Naprawdę powolne metody . Metody wykorzystujące charindex, patindex lub LIKE.

Naprawdę nie mogę polecić wystarczająco dużo, aby przeczytać artykuł, aby dowiedzieć się o kompromisach między wszystkimi tymi opcjami.

Tak, Twoje obecne rozwiązanie jest podatne na ataki typu SQL injection.

Najlepszym rozwiązaniem, które znalazłem, jest użycie funkcji, która dzieli tekst na słowa (jest ich kilka zamieszczonych tutaj lub możesz użyć tego z mojego bloga ), a następnie dołączenie tego do swojej tabeli. Coś jak:

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

Jedną z metod, które warto rozważyć, jeśli zamierzasz dużo pracować z wartościami, jest zapisanie ich najpierw w tabeli tymczasowej. Następnie po prostu dołącz do tego jak zwykle.

W ten sposób parsujesz tylko raz.

Najłatwiej jest użyć jednego z UDF 'Split', ale tak wiele osób zamieściło ich przykłady, pomyślałem, że pójdę inną drogą;)

Ten przykład utworzy tymczasową tabelę, do której możesz dołączyć (#tmpDept) i wypełni ją identyfikatorami działów, które przekazałeś. Zakładam, że oddzielasz je przecinkami, ale możesz - oczywiście - zmienić to co chcesz.

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 @DeptID=@DepartmentIDs
    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

Umożliwi to przekazanie jednego identyfikatora działu, wielu identyfikatorów z przecinkami między nimi, a nawet wielu identyfikatorów z przecinkami i spacjami między nimi.

Więc jeśli zrobiłeś coś takiego:

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

Zobaczysz nazwy wszystkich przekazanych identyfikatorów działów ...

Ponownie, można to uprościć, używając funkcji do zapełnienia tabeli tymczasowej ... Zrobiłem to głównie bez niej, aby zabić nudę :-P

- Kevin Fairchild

Możesz użyć XML.

Na przykład

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) 

Polecenie sp_xml_preparedocument jest wbudowane.

To dałoby wynik:

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

który ma wszystko (więcej?) tego, czego potrzebujesz.

Superszybka metoda XML, jeśli chcesz użyć procedury składowanej i przekazać listę identyfikatorów działów oddzielonych przecinkami:

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

Wszystko zasługa bloga Guru Brada Schulza

Spróbuj tego:

@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)  +'%')

bardzo prosty.