Delete multiple rows from a table in sql server with a single call from client application

Question

Here is my requirement:

• I want to delete multiple rows of a table
• I have the list of all ids that needs to be deleted in the client app.
• All I want is to make just a single call from my C# client app to delete all these records.

What I had earlier:

foreach (var id in idList)
{
//Call a Stored Procedure which takes the id
//as parameter and deletes the record
}

This is a problem because there is a database hit for each item in loop.

Then I started using the SQL Server 2008 newly introduced Table Types and Table Valued Parameters for stored procedures for this.

• First have a Table Type created in the database which has a single column (of type of Id)
• Create a new Stored Procedure which accepts this new table type (Table Valued Parameter)
• In the Client code, create a list/Datatable with all the ids to be deleted.
• Make a single call to the database to make use of the new stored Procedure.

It is pretty straight forward in the client code, which goes something like this:

SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = System.Data.SqlDbType.Structured;

OK - Though I achieved what I set for, this definitely does not work for SQL server versions before 2008.

So what I did was to introduce a fall back mechanism:

• I introduced a Stored Procedure that takes comma separated id values
• My client code will create such comma separated ids, and then simply pass it to the new stored proc.
• The Stored Proc will then split all those ids, and then call delete one by one.
• So in a way, I have achieved what I had set for even on SQL Server version prior to 2008: delete multiple rows with a single database hit.

But I am not that convinced with the last approach I took - A quick search on the internet did not reveal anything much.

So can somebody tell me if I am doing the right thing with comma separated ids and all.
What is the usual best practice to delete multiple records from the client app with a single database hit, provided the ids are known.

Thanks in advance.

Answer 1

hi you just concatenate the all values in the list with delemeter as follows 1,2,3,4,5

here is the sample store procdure for deleteing multiple rows

create PROCEDURE deleteusers  --deleteusers '1,2,3,4'
    -- Add the parameters for the stored procedure here
    @UsersIds varchar(max)

AS
BEGIN
declare @abc varchar(max)
set @abc='delete from users where userid in('+@UsersIds+')'
exec(@abc)
END
GO

It will delete the users with userid 1,2,3,4

Answer 2

You can send a string containing comma separated or some other character(to be used for split) to send ids from C# end.

Create Procedure deleteusers
(
@ids nvarchar(MAX)
)
as
begin
DECLARE  @pos bigint,@count int,@id varchar(max),@I int,@Spos int
set @Spos=1
set @I=1
set @count=  len(@ids)-len(REPLACE(@ids,',','')) 
while(@I<=@count)
    begin
        SET @pos = CHARINDEX(',', @ids, 1)


        set @id = substring(@ids,@Spos,@pos-1)
delete from  TableName where id=@id 
set @ids= stuff(@ids, @Spos, @pos,'')

        set @I=@I+1
end
end

Answer 3

If you have a column delimited list of values, say @list, you can do what you want with this delete statement:

delete from t
where ','+@list+',' like '%,'+cast(t.id as varchar(255))+',%'

This is emulating the in expression with string operations.

The downside is that this requires a full-table scan. It will not use an index on id.

Answer 4

The Dynamic SQL option listed here is probably your best bet but just to give you one more option.

CREATE PROCEDURE deleteusers  --deleteusers '1,2,3,4'
-- Add the parameters for the stored procedure here
@UsersIds varchar(max)

CREATE TABLE #DeleteUsers (UserId Int)

-- Split @UserIds and populate into the #DeleteUsers temp table

DELETE FROM Users WHERE UserId IN (SELECT UserId FROM #DeleteUsers)

Answer 5

Rather than build SQL strings in code, I'd suggest looking at an ORM (object-relational-mapper), such as the Entity Framework or NHibernate.