Como passar uma matriz em um procedimento armazenado do SQL Server

Como passar uma matriz em um procedimento armazenado do SQL Server?

Por exemplo, tenho uma lista de funcionários. Eu quero usar essa lista como uma tabela e juntá-lo com outra tabela. Mas a lista de funcionários deve ser passada como parâmetro do C #.

Como você já tem uma Lista, acho que existem maneiras mais diretas do que XML.

SQL Server 2008 (ou mais recente)

Primeiro, no seu database, crie os dois objects a seguir:

CREATE TYPE dbo.EmployeeList AS TABLE ( EmployeeID INT ); GO CREATE PROCEDURE dbo.DoSomethingWithEmployees @List AS dbo.EmployeeList READONLY AS BEGIN SET NOCOUNT ON; SELECT EmployeeID FROM @List; END GO 

Agora no seu código c #:

 DataTable tvp = new DataTable(); // define / populate DataTable from your List here using (conn) { SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp); tvparam.SqlDbType = SqlDbType.Structured; // execute query, consume results, etc. here } 

SQL Server 2005

Se você estiver usando o SQL Server 2005, eu ainda recomendaria uma function dividida sobre XML. Primeiro, crie uma function:

 CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = xivalue('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL ); GO 

Agora seu procedimento armazenado pode ser apenas:

 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); END GO 

E no seu código c # você só precisa passar a lista como '1,2,3,12'

Eu recomendo que você compare a manutenção e o desempenho dessas opções com o método selecionado.

Com base na minha experiência, criando uma expressão delimitada a partir dos employeeIDs, há uma solução complicada e agradável para esse problema. Você deve criar apenas uma expressão de string como ';123;434;365;' em que 123 , 434 e 365 são alguns employeeIDs. Ao chamar o procedimento abaixo e passar essa expressão para ele, você pode buscar seus registros desejados. Facilmente você pode juntar a “outra tabela” nesta consulta. Esta solução é adequada em todas as versões do SQL Server. Além disso, em comparação com o uso de tabela variável ou tabela temporária, é uma solução muito mais rápida e otimizada.

 CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees @List AS varchar(max) AS BEGIN SELECT EmployeeID FROM EmployeesTable -- inner join AnotherTable on ... where @List like '%;'+cast(employeeID as varchar(20))+';%' END GO 

Use um parâmetro com valor de tabela para o procedimento armazenado.

Quando você passá-lo de C #, você adicionará o parâmetro com o tipo de dados de SqlDb.Structured.

Veja aqui: http://msdn.microsoft.com/pt-br/library/bb675163.aspx

Exemplo:

 // Assumes connection is an open SqlConnection object. using (connection) { // Create a DataTable with the modified rows. DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added); // Configure the SqlCommand and SqlParameter. SqlCommand insertCommand = new SqlCommand( "usp_InsertCategories", connection); insertCommand.CommandType = CommandType.StoredProcedure; SqlParameter tvpParam = insertCommand.Parameters.AddWithValue( "@tvpNewCategories", addedCategories); tvpParam.SqlDbType = SqlDbType.Structured; // Execute the command. insertCommand.ExecuteNonQuery(); } 

Você precisa passá-lo como um parâmetro XML.

Editar: código rápido do meu projeto para ter uma ideia:

 CREATE PROCEDURE [dbo].[GetArrivalsReport] @DateTimeFrom AS DATETIME, @DateTimeTo AS DATETIME, @HostIds AS XML(xsdArrayOfULong) AS BEGIN DECLARE @hosts TABLE (HostId BIGINT) INSERT INTO @hosts SELECT arrayOfUlong.HostId.value('.','bigint') data FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId) 

Então você pode usar a tabela temporária para se juntar às suas tabelas. Definimos arrayOfUlong como um esquema XML integrado para manter a integridade dos dados, mas você não precisa fazer isso. Eu recomendo usá-lo então aqui está um código rápido para garantir que você sempre obtenha um XML com longs.

 IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong') BEGIN CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong] AS N'        '; END GO 

O contexto é sempre importante, como o tamanho e a complexidade do array. Para listas de tamanho pequeno a médio, várias das respostas postadas aqui estão bem, embora alguns esclarecimentos devam ser feitos:

  • Para dividir uma lista delimitada, um divisor baseado em SQLCLR é o mais rápido. Existem inúmeros exemplos ao redor, se você quiser escrever o seu próprio, ou você pode simplesmente fazer o download da biblioteca livre SQL # de funções CLR (que eu escrevi, mas a function String_Split, e muitos outros, são totalmente gratuitos).
  • Dividir arrays baseados em XML pode ser rápido, mas você precisa usar XML baseado em atributos, não XML baseado em elementos (que é o único tipo mostrado nas respostas aqui, embora o exemplo XML de @ AaronBertrand seja o melhor, pois seu código está usando o function XML text() Para mais informações (ou seja, análise de desempenho) sobre o uso de XML para listas de divisão, verifique “Usando XML para passar listas como parâmetros no SQL Server” por Phil Factor.
  • Usar o TVPs é ótimo (supondo que você esteja usando pelo menos o SQL Server 2008, ou mais recente), pois os dados são transmitidos para o proc e aparecem pré-analisados ​​e fortemente tipados como uma variável de tabela. No entanto, na maioria dos casos, armazenar todos os dados no DataTable significa duplicar os dados na memory como ele é copiado da coleção original. Portanto, usar o método DataTable de transmissão em TVPs não funciona bem para conjuntos maiores de dados (ou seja, não dimensiona bem).
  • XML, ao contrário de listas delimitadas simples de Ints ou Strings, pode manipular mais de matrizes unidimensionais, assim como as TVPs. Mas, assim como o método DataTable TVP, o XML não é dimensionado bem, pois mais que duplica o tamanho de dados na memory, já que ele precisa considerar adicionalmente a sobrecarga do documento XML.

Com tudo isso dito, se os dados que você está usando são grandes ou não são muito grandes, mas crescem consistentemente, então o método IEnumerable TVP é a melhor escolha, pois ele transmite os dados para o SQL Server (como o método DataTable ), MAS não requer qualquer duplicação da coleção na memory (diferente de qualquer outro método). Eu postei um exemplo do código SQL e C # nesta resposta:

Passar dictionary para procedimento armazenado T-SQL

Não há suporte para array no sql server, mas existem várias maneiras pelas quais você pode passar a coleção para um proc armazenado.

  1. Usando datatable
  2. Usando XML.Try convertendo sua coleção em um formato xml e, em seguida, passá-lo como uma input para um procedimento armazenado

O link abaixo pode ajudá-lo

passando coleção para um procedimento armazenado

Eu tenho procurado através de todos os exemplos e respostas de como passar qualquer array para o sql server sem o incômodo de criar um novo tipo de tabela, até que encontrei esta linK , abaixo está como eu o apliquei no meu projeto:

– O código a seguir irá obter um Array como Parâmetro e inserir os valores de – array em outra tabela

 Create Procedure Proc1 @UserId int, //just an Id param @s nvarchar(max) //this is the array your going to pass from C# code to your Sproc AS declare @xml xml set @xml = N'' + replace(@s,',','') + '' Insert into UserRole (UserID,RoleID) select @UserId [UserId], t.value('.','varchar(max)') as [RoleId] from @xml.nodes('//root/r') as a(t) END 

Espero que goste

Isso ajudará você. 🙂 Siga os próximos passos,

  1. Abra o criador de consultas
  2. Copiar Cole o código a seguir como ele é, ele irá criar a function que converte a seqüência de caracteres para Int

     CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = xivalue('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL ); GO 
  3. Crie o seguinte procedimento armazenado

      CREATE PROCEDURE dbo.sp_DeleteMultipleId @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); END GO 
  4. Execute este SP Usando exec sp_DeleteId '1,2,3,12' esta é uma string de Id’s que você deseja apagar,

  5. Você converter sua matriz para string em C # e passá-lo como um parâmetro Stored Procedure

     int[] intarray = { 1, 2, 3, 4, 5 }; string[] result = intarray.Select(x=>x.ToString()).ToArray(); 

     SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "sp_DeleteMultipleId"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ; 

Isso excluirá várias linhas, tudo de melhor

Levei muito tempo para descobrir isso, então no caso de alguém precisar …

Isto é baseado no método SQL 2005 na resposta de Aaron, e usando sua function SplitInts (acabei de remover o parâmetro delim pois sempre usarei vírgulas). Estou usando o SQL 2008, mas queria algo que funcionasse com datasets tipados (XSD, TableAdapters) e sei que os parâmetros de string funcionam com eles.

Eu estava tentando fazer com que sua function trabalhasse em uma cláusula do tipo “where in (1,2,3)”, e não tendo sorte de maneira direta. Então, criei uma tabela temporária primeiro e fiz uma junit interna em vez de “where in”. Aqui está o meu uso de exemplo, no meu caso eu queria obter uma lista de receitas que não contenham certos ingredientes:

 CREATE PROCEDURE dbo.SOExample1 ( @excludeIngredientsString varchar(MAX) = '' ) AS /* Convert string to table of ints */ DECLARE @excludeIngredients TABLE (ID int) insert into @excludeIngredients select ID = Item from dbo.SplitInts(@excludeIngredientsString) /* Select recipies that don't contain any ingredients in our excluded table */ SELECT r.Name, r.Slug FROM Recipes AS r LEFT OUTER JOIN RecipeIngredients as ri inner join @excludeIngredients as ei on ri.IngredientID = ei.ID ON r.ID = ri.RecipeID WHERE (ri.RecipeID IS NULL)