0

Possible Duplicate:
Parameterizing a SQL IN clause?

I need to delete a multiple rows in a sql table from asp.net in a single click.How to write a stored procedure for this? but i should use "IN" keyword so that i can delete multiple rows at a time.so if i create a SP containing delete query with "IN" keyword, in what format should i pass the input parameter?

Community
  • 1
  • 1
idhris
  • 1
  • 1

1 Answers1

1

Thanks for tagging with your specific SQL version! It's very helpful because this is one area where the best way to do this for optimal performance has been changing with every major release of SQL.

In SQL 2000, the typical approach was to pass in a string of comma separated values (CSV) and then use a UDF in the SQL database to unpack them into a table.

In SQL 2008, the new hotness is to use table-valued parameters, which let you pass the data in as a table/recordset directly. Makes for the nicest code to read, and performs the best.

But since you are on SQL 2005, you can't use table valued parameters. So your best option is to use the XML data type. That handily outperforms the CSV method. Here's a little sample:

CREATE PROCEDURE [dbo].[uspDeleteEmployee]
@RecordIDs XML
AS
BEGIN

    DELETE dbo.Employee  
    FROM dbo.Employee e
    INNER JOIN @RecordIDs.nodes('RecordList/ID') AS x(Item) 
    ON e.EmployeeID = Item.value('.', 'int' )
END
GO

And here's a sample of the XML string:

'<RecordList><ID>1</ID><ID>23</ID><ID>78</ID></RecordList>'
Jonathan Van Matre
  • 942
  • 5
  • 14