Passare una serie di parametri a una stored procedure

Devo passare una serie di “id” a una stored procedure, per eliminare tutte le righe dalla tabella TRANNE le righe che corrispondono all’id nell’array.

Come posso farlo in un modo molto semplice?

Utilizzare una stored procedure:

EDIT: un complemento per serializzare lista (o qualsiasi altra cosa):

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); string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray()); 

Il risultato (pronto per l’uso con il parametro XML):

   1 2 3  

POST ORIGINALE:

Passando XML come parametro:

  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 

Se si utilizza Sql Server 2008 o versione successiva, è ansible utilizzare qualcosa chiamato parametro con valori di tabella (TVP) anziché serializzare e deserializzare i dati dell’elenco ogni volta che si desidera passarlo a una stored procedure.

Iniziamo creando un semplice schema per fungere da terreno di gioco:

 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 

Con il nostro schema e i dati di esempio, siamo ora pronti per creare la nostra procedura memorizzata 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 

Con il nostro schema e le API in atto, possiamo chiamare la procedura memorizzata TVP dal nostro programma in questo modo:

  // 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.ToString( ) ); } /* Output: * Matching t1ID: 1 * Matching t1ID: 5 * Matching t1ID: 9 */ 

C’è probabilmente un modo meno doloroso per farlo usando un’API più astratta, come Entity Framework. Tuttavia, non ho il tempo di vedere di persona in questo momento.

questa è la migliore fonte:

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

crea una funzione divisa usando il link e usala come:

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

Preferisco l’approccio alla tabella dei numeri

Questo è il codice basato sul link qui sopra che dovrebbe farlo per te …

Prima di utilizzare la mia funzione, devi configurare una tabella “helper”, devi farlo una sola volta per 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 @[email protected]+1 INSERT INTO Numbers VALUES (@x) END 

usa questa funzione per dividere la tua stringa, che non esegue il loop ed è molto veloce:

 CREATE FUNCTION [dbo].[FN_ListToTable] ( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000) --REQUIRED, the list to split apart ) RETURNS @ParsedList table ( ListValue varchar(500) ) AS BEGIN /** Takes the given @List string 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

puoi usare questa funzione come una tabella in un join:

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

ecco la tua cancellazione:

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

Potresti provare questo:

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

È ansible utilizzare una tabella temporanea che la stored procedure prevede esistere. Funzionerà su versioni precedenti di SQL Server, che non supportano XML, ecc.

 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 

Prenderò in considerazione il passaggio degli ID come una stringa XML e quindi si potrà distruggere l’XML in una tabella temporanea a cui partecipare, oppure si potrebbe anche eseguire una query sull’XML direttamente utilizzando SP_XML_PREPAREDOCUMENT e OPENXML .

Che dire dell’utilizzo del tipo di dati XML invece di passare un array. Trovo che una soluzione migliore e funziona bene in SQL 2005

Mi piace questo, perché è adatto per essere passato come XElement, che è adatto per SqlCommand

(Scusate, è VB.NET ma avete capito)

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

Questo è il proc di sql Stored, abbreviato, non completo!

CREATE PROCEDURE [dbo]. [Myproc] (@blah xml)
AS … WHERE SomeID IN (SELEZIONA doc.t.value (‘.’, ‘Int’) da @ netwerkids.nodes (N ‘/ doc / d’) come doc (t))

In SQL Server 2016 è ansible racchiudere array con [] e passarlo come JSON vedere http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures -come-json.aspx