Procedura memorizzata T-SQL che accetta più valori di identificazione

Esiste un modo elegante per gestire il passaggio di un elenco di ID come parametro a una stored procedure?

Ad esempio, voglio i reparti 1, 2, 5, 7, 20 restituiti dalla procedura memorizzata. In passato, ho passato in una lista delimitata da virgole di id, come il codice qui sotto, ma mi sento davvero sporco a farlo.

SQL Server 2005 è la mia unica limitazione applicabile, credo.

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 ha mantenuto la risposta autorevole a questa domanda negli ultimi 16 anni: Array ed elenchi in SQL Server .

Esistono almeno una dozzina di modi per passare un array o un elenco a una query; ognuno ha i suoi pro e contro unici.

  • Parametri con valori di tabella . Solo SQL Server 2008 e versioni successive, e probabilmente il più vicino a un approccio “migliore” universale.
  • Il metodo iterativo . Passa una stringa delimitata e passa attraverso di essa.
  • Utilizzando il CLR . SQL Server 2005 e versioni successive solo da lingue .NET.
  • XML . Ottimo per inserire molte righe; potrebbe essere eccessivo per SELECTs.
  • Tabella dei numeri . Prestazioni / complessità più elevate rispetto al semplice metodo iterativo.
  • Elementi a lunghezza fissa . La lunghezza fissa migliora la velocità sulla stringa delimitata
  • Funzione dei numeri . Variazioni di tabella di numeri e lunghezza fissa in cui il numero viene generato in una funzione anziché prelevato da una tabella.
  • Espressione tabella comune ricorsiva (CTE). SQL Server 2005 e versioni successive, ancora non troppo complesse e prestazioni più elevate rispetto al metodo iterativo.
  • SQL dinamico . Può essere lento e ha implicazioni sulla sicurezza.
  • Passando l’elenco come molti parametri . Tedioso e sobject a errori, ma semplice.
  • Metodi veramente lenti . Metodi che usano charindex, patindex o LIKE.

Non posso davvero raccomandare abbastanza per leggere l’articolo per conoscere i compromessi tra tutte queste opzioni.

Sì, la tua soluzione attuale è soggetta agli attacchi di SQL injection.

La soluzione migliore che ho trovato è quella di utilizzare una funzione che divide il testo in parole (ce ne sono alcuni pubblicati qui, oppure puoi usare questo dal mio blog ) e poi aggiungerlo al tuo tavolo. Qualcosa di simile a:

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

Un metodo che potresti voler considerare se lavorerai con i valori molto è scriverli prima su una tabella temporanea. Quindi ti unisci a lui come al solito.

In questo modo, stai solo analizzando una volta.

È più facile utilizzare una delle UDF “Split”, ma così tante persone hanno pubblicato esempi di questi, ho pensato che avrei intrapreso un percorso diverso;)

Questo esempio creerà una tabella temporanea per l’adesione a (#tmpDept) e la riempirà con gli ID del dipartimento che hai passato. Suppongo che tu li stia separando con virgole, ma puoi – ovviamente – cambiare a tutto ciò che vuoi.

 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 

Questo ti permetterà di passare in un ID di reparto, più ID con virgole tra loro, o anche più ID con virgole e spazi tra di loro.

Quindi se hai fatto qualcosa del tipo:

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

Vedresti i nomi di tutti gli ID di dipartimento che hai passato in …

Di nuovo, questo può essere semplificato usando una funzione per popolare il tavolo temporaneo … L’ho fatto principalmente senza uno solo per uccidere un po ‘di noia 😛

– Kevin Fairchild

Potresti usare XML.

Per esempio

 declare @xmlstring as varchar(100) set @xmlstring = '-1' declare @docid int exec sp_xml_preparedocument @docid output, @xmlstring select [id],parentid,nodetype,localname,[text] from openxml(@docid, '/args', 1) 

Il comando sp_xml_preparedocument è integrato.

Questo produrrebbe l’output:

 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 

che ha tutto (di più?) di ciò che ti serve.

Un metodo XML superveloce, se si desidera utilizzare una stored procedure e passare l’elenco separato da virgola di ID reparto:

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

Tutto il merito va al blog di Guru Brad Schulz

Prova questo:

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

molto semplice.