Passing an array of parameters to a stored procedure

Question

I need to pass an array of "id's" to a stored procedure, to delete all rows from the table EXCEPT the rows that match id's in the array.

How can I do it in a most simple way?

Answer 1

If you are using Sql Server 2008 or better, you can use something called a Table-Valued Parameter (TVP) instead of serializing & deserializing your list data every time you want to pass it to a stored procedure.

Let's start by creating a simple schema to serve as our playground:

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 

With our schema and sample data in place, we are now ready to create our TVP stored procedure:

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 

With both our schema and API in place, we can call the TVP stored procedure from our program like so:

        // 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    */ 

There is probably a less painful way to do this using a more abstract API, such as Entity Framework. However, I do not have the time to see for myself at this time.