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. :)

Which DBMS are you using? Postgres? Oracle? – Dec 20 '14 at 11:54 — , Dec 20 '14 at 11:54

Charan Ghate · Answer 1 · 2014-12-20T11:37:20.810

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

Open the Query Designer
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

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

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

In your solution how to pass array to store procedure.my id is int type. I saw this link before. but I have problem yet. — Bahare Aghabarari, Dec 20 '14 at 07:32
Do you want to pass array to stored procedure from Code Behind? — Charan Ghate, Dec 20 '14 at 08:54
Can you explain about dbo.SplitInts? I pass value from code behind with string. not string[]. also i change id types from int to double. — Bahare Aghabarari, Dec 21 '14 at 05:40
You can use your logic to convert the int array to single string seperated by comma(,) — Charan Ghate, Dec 22 '14 at 06:53

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

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

edited May 23 '17 at 11:49

Community

1
1

answered Dec 20 '14 at 08:57

Hardik Patel

317
3
12

score 0 · Answer 3 · answered Dec 20 '14 at 09:04

0

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

answered Dec 20 '14 at 09:04

Charan Ghate

1,384
15
32

No, Ids are different value any time! – Bahare Aghabarari Dec 20 '14 at 09:20

score 0 · Accepted Answer · answered Dec 23 '14 at 21:42

0

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

answered Dec 23 '14 at 21:42

Bahare Aghabarari

19
5

How to pass an array to sql?

4 Answers4