Passer un tableau de parameters à une procédure stockée

J’ai besoin de passer un tableau de “identifiants” à une procédure stockée, pour supprimer toutes les lignes de la table, SAUF les lignes correspondant aux identifiants du tableau.

Comment puis-je le faire de la manière la plus simple?

Utilisez une procédure stockée:

EDIT: Un complément pour la liste de sérialisation (ou toute autre chose):

List testList = new List(); testList.Add(1); testList.Add(2); testList.Add(3); XmlSerializer xs = new XmlSerializer(typeof(List)); MemoryStream ms = new MemoryStream(); xs.Serialize(ms, testList); ssortingng resultXML = UTF8Encoding.UTF8.GetSsortingng(ms.ToArray()); 

Le résultat (prêt à utiliser avec le paramètre XML):

   1 2 3  

POSTE ORIGINAL:

Passer XML en paramètre:

  1 2  

 CREATE PROCEDURE [dbo].[DeleteAllData] ( @XMLDoc XML ) AS BEGIN DECLARE @handle INT EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc DELETE FROM YOURTABLE WHERE YOUR_ID_COLUMN NOT IN ( SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.') ) EXEC sp_xml_removedocument @handle 

Si vous utilisez Sql Server 2008 ou supérieur, vous pouvez utiliser un paramètre appelé TVP (Table-Valued Parameter) au lieu de sérialiser et désérialiser vos données de liste chaque fois que vous souhaitez les transmettre à une procédure stockée.

Commençons par créer un schéma simple pour servir de terrain de jeu:

 CREATE DATABASE [TestbedDb] GO USE [TestbedDb] GO /* First, setup the sample program's account & credentials*/ CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×? ?S[°¿Q¥½q?_Ĭ¼Ð)3õļ%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N'db_owner', N'testbedUser' GO /* Now setup the schema */ CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY ); GO INSERT INTO dbo.Table1 (t1Id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); GO 

Avec notre schéma et nos exemples de données en place, nous sums maintenant prêts à créer notre procédure stockée TVP:

 CREATE TYPE T1Ids AS Table ( t1Id INT ); GO CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY ) AS BEGIN SET NOCOUNT ON; SELECT Table1.t1Id FROM dbo.Table1 AS Table1 JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id; END GO 

Avec notre schéma et notre API en place, nous pouvons appeler la procédure stockée TVP de notre programme comme suit:

  // Curry the TVP data DataTable t1Ids = new DataTable( ); t1Ids.Columns.Add( "t1Id", typeof( int ) ); int[] listOfIdsToFind = new[] {1, 5, 9}; foreach ( int id in listOfIdsToFind ) { t1Ids.Rows.Add( id ); } // Prepare the connection details SqlConnection testbedConnection = new SqlConnection( @"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" ); try { testbedConnection.Open( ); // Prepare a call to the stored procedure SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1", testbedConnection ); findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure; // Curry up the TVP parameter SqlParameter sqlParameter = new SqlParameter( "Table1Ids", t1Ids ); findMatchingRowsInTable1.Parameters.Add( sqlParameter ); // Execute the stored procedure SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( ); while ( sqlDataReader.Read( ) ) { Console.WriteLine( "Matching t1ID: {0}", sqlDataReader[ "t1Id" ] ); } } catch ( Exception e ) { Console.WriteLine( e.ToSsortingng( ) ); } /* Output: * Matching t1ID: 1 * Matching t1ID: 5 * Matching t1ID: 9 */ 

Il existe probablement un moyen moins pénible de le faire en utilisant une API plus abstraite, telle que Entity Framework. Cependant, je n’ai pas le temps de voir par moi-même en ce moment.

c’est la meilleure source:

http://www.sommarskog.se/arrays-in-sql.html

créer une fonction split en utilisant le lien et l’utiliser comme:

 DELETE YourTable FROM YourTable d LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value WHERE s.Value IS NULL 

Je préfère l’approche de la table des nombres

Ceci est un code basé sur le lien ci-dessus qui devrait le faire pour vous …

Avant d’utiliser ma fonction, vous devez configurer une table “helper”, il vous suffit de le faire une fois par firebase database:

 CREATE TABLE Numbers (Number int NOT NULL, CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] DECLARE @x int SET @x=0 WHILE @x<8000 BEGIN SET @x=@x+1 INSERT INTO Numbers VALUES (@x) END 

utilisez cette fonction pour diviser votre chaîne, qui ne boucle pas et est très rapide:

 CREATE FUNCTION [dbo].[FN_ListToTable] ( @SplitOn char(1) --REQUIRED, the character to split the @List ssortingng on ,@List varchar(8000) --REQUIRED, the list to split apart ) RETURNS @ParsedList table ( ListValue varchar(500) ) AS BEGIN /** Takes the given @List ssortingng and splits it apart based on the given @SplitOn character. A table is returned, one row per split item, with a column name "ListValue". This function workes for fixed or variable lenght items. Empty and null items will not be included in the results set. Returns a table, one row per item in the list, with a column name "ListValue" EXAMPLE: ---------- SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,

,,,,B’) returns: ListValue ———– 1 12 123 1234 54321 6 A *

B (10 row(s) affected) **/ —————- –SINGLE QUERY– –this will not return empty rows —————- INSERT INTO @ParsedList (ListValue) SELECT ListValue FROM (SELECT LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number – 1))) AS ListValue FROM ( SELECT @SplitOn + @List + @SplitOn AS List2 ) AS dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = @SplitOn ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' RETURN END --Function FN_ListToTable

vous pouvez utiliser cette fonction comme table dans une jointure:

 SELECT Col1, COl2, Col3... FROM YourTable INNER JOIN dbo.FN_ListToTable(',',@YourSsortingng) s ON YourTable.ID = s.ListValue 

voici votre suppression:

 DELETE YourTable FROM YourTable d LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue WHERE s.ListValue IS NULL 

Vous pourriez essayer ceci:

 DECLARE @List VARCHAR(MAX) SELECT @List = '1,2,3,4,5,6,7,8' EXEC( 'DELETE FROM TABLE WHERE ID NOT IN (' + @List + ')' ) 

Vous pouvez utiliser une table temporaire que la procédure stockée prévoit d’exister. Cela fonctionnera sur les anciennes versions de SQL Server, qui ne prennent pas en charge XML, etc.

 CREATE TABLE #temp (INT myid) GO CREATE PROC myproc AS BEGIN DELETE YourTable FROM YourTable LEFT OUTER JOIN #temp T ON T.myid=s.id WHERE s.id IS NULL END 
 declare @ids nvarchar(1000) set @ids = '100,2,3,4,5' --Parameter passed set @ids = ',' + @ids + ',' select * from TableName where charindex(',' + CAST(Id as nvarchar(50)) + ',', @ids) > 0 

J’envisagerais de transmettre vos identifiants en tant que chaîne XML, puis vous pourriez détruire le code XML en une table temporaire à laquelle vous pourriez adhérer, ou vous pourriez également interroger directement le XML à l’aide de SP_XML_PREPAREDOCUMENT et OPENXML .

Qu’en est-il de l’utilisation du type de données XML au lieu de passer un tableau. Je trouve cela une meilleure solution et fonctionne bien dans SQL 2005

J’aime celui-ci, car il est adapté pour être passé en tant que XElement, ce qui convient à SqlCommand

(Désolé, c’est VB.NET mais vous avez l’idée)

  Public Function ToXml(Of T)(array As IEnumerable(Of T)) As XElement Return XElement.Parse( Ssortingng.Format("{0}", Ssortingng.Join("", array.Select(Function(s) Ssortingng.Concat("", s.ToSsortingng(), "")))), LoadOptions.None) End Function 

Ceci est le processus stocké SQL, raccourci, pas complet!

CREATE PROCEDURE [dbo]. [Myproc] (@blah xml)
AS … O Some SomeID IN (SELECT doc.t.value (‘.’, ‘Int’) de @ netwerkids.nodes (N ‘/ doc / d’) en tant que doc (t))

Dans SQL Server 2016, vous pouvez encapsuler un tableau avec [] et le transmettre en tant que JSON, voir http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures -as-json.aspx