Procedimento armazenado T-SQL que aceita vários valores de identificação

Existe uma maneira elegante de manipular a passagem de uma lista de ids como um parâmetro para um procedimento armazenado?

Por exemplo, eu quero os departamentos 1, 2, 5, 7, 20 retornados pelo meu procedimento armazenado. No passado, passei em uma lista de IDs delimitada por vírgulas, como o código abaixo, mas me sinto realmente suja fazendo isso.

O SQL Server 2005 é a minha única limitação aplicável, eu acho.

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 manteve a resposta oficial a essa pergunta nos últimos 16 anos: Arrays e Lists no SQL Server .

Há pelo menos uma dúzia de maneiras de passar uma matriz ou lista para uma consulta; cada um tem seus próprios prós e contras.

  • parameters com valor de tabela . SQL Server 2008 e superior, e provavelmente o mais próximo de uma abordagem “melhor” universal.
  • O Método Iterativo . Passe uma string delimitada e passe por ela.
  • Usando o CLR . SQL Server 2005 e superior somente de linguagens .NET.
  • XML Muito bom para inserir muitas linhas; pode ser um exagero para SELECTs.
  • Tabela de números . Maior desempenho / complexidade do que o método iterativo simples.
  • Elementos de comprimento fixo . Comprimento fixo melhora a velocidade sobre a cadeia delimitada
  • Função dos números . Variações da tabela de números e comprimento fixo em que o número é gerado em uma function, em vez de ser retirado de uma tabela.
  • Expressão de Tabela Comum Recursiva (CTE). SQL Server 2005 e superior, ainda não é muito complexo e maior desempenho do que o método iterativo.
  • SQL dynamic Pode ser lento e tem implicações de segurança.
  • Passando a lista como muitos parâmetros . Tédio e propenso a erros, mas simples.
  • Métodos Realmente Lentos . Métodos que usam charindex, patindex ou LIKE.

Eu realmente não posso recomendar o suficiente para ler o artigo para aprender sobre as compensações entre todas essas opções.

Sim, sua solução atual é propensa a ataques de injeção de SQL.

A melhor solução que encontrei é usar uma function que divide o texto em palavras (há algumas postadas aqui, ou você pode usar essa no meu blog ) e, em seguida, junte-as à sua tabela. Algo como:

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

Um método que você pode querer considerar se vai trabalhar muito com os valores é escrevê-los para uma tabela temporária primeiro. Então você acabou de entrar nele como normal.

Desta forma, você está apenas analisando uma vez.

É mais fácil usar uma das UDFs ‘Divididas’, mas muitas pessoas publicaram exemplos daquelas, eu imaginei que seguiria uma rota diferente;)

Este exemplo irá criar uma tabela temporária para você se juntar a (#tmpDept) e preenchê-lo com o id do departamento que você passou. Estou assumindo que você está separando-os com vírgulas, mas você pode – claro – mudar para o que você quiser.

 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 

Isso permitirá que você passe em um ID de departamento, vários IDs com vírgulas entre eles ou até mesmo vários IDs com vírgulas e espaços entre eles.

Então, se você fez algo como:

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

Você veria os nomes de todos os IDs de departamento nos quais você passou …

Novamente, isso pode ser simplificado usando uma function para preencher a tabela temporária … Eu principalmente fiz isso sem apenas um para matar algum tédio: -P

– Kevin Fairchild

Você poderia usar XML.

Por exemplo

 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) 

O comando sp_xml_preparedocument está embutido.

Isso produziria a saída:

 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 

qual tem tudo (mais?) do que você precisa.

Um método XML super rápido, se você quiser usar um procedimento armazenado e passar a lista separada por vírgula de IDs de departamento:

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

Todo o crédito vai para o Blog do Guru Brad Schulz

Tente este:

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

muito simples.