How to pass an array to sql?

Question

I'm beginner in sql. I want that deleteseveral rows from my table Once (no One to One). I think that the best way is use array. I want pass Id of rows (of type int) to store procedure with array. I search and find several way. but don't find my sulotion yet. I Thanks any one that guide me. :)

Answer 1

This will help you. :) Follow the next steps,

1. Open the Query Designer

2. Copy Paste the Following code as it is,it will create the Function which convert the String to Int

   CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./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. Create the Following stored procedure

    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 this SP Using exec sp_DeleteId '1,2,3,12' this is a string of Id's which you want to delete,

5. You convert your array to string in C# and pass it as a Stored Procedure parameter

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 ;

This will delete multiple rows, All the best

Answer 2

You can use XMl datatype,to pass Int Array list. Bind your ID list in XML object.Pass it to SP. And in SP , Take Node from XML parameter. You can refere below link.

Passing an array of parameters to a stored procedure

Answer 3

If your Id's array start from 1 and end to 10 You can use the following code in store Procedure

If your Id's array start from 1 and end to 10 You can use the following code in store Procedure

AS
 BEGIN
  DECLARE @Count NUMERIC(18,0)
  SET @Count = 1
       WHILE(@Count <= 10 )
       BEGIN
           DELETE FROM TableName WHERE Id=@Count
           SET @Count=@Count+1
       END
    END    
END

Answer 4

That's a link that I found useful. it solve my problem. I hope that it helpful for you,too :) http://www.codeproject.com/Questions/631284/How-to-pass-an-array-to-SQL-SERVER-stored-pro