Can I first SELECT and then DELETE records in a single t-SQL transaction?

Question

I cannot understand if this is an acceptable operation. I need to select records from a SQL Server 2008 database and then delete them as a single transaction from ASP.NET code. Note that .NET code must be able to retrieve the data that was first selected.

Something like that:

SELECT * FROM [tbl] WHERE [id] > 6; DELETE FROM [tbl] WHERE [id] > 6 

I tried it using SQL Fiddle, but if so:

 SELECT * FROM [tbl] 

I get the full table as if nothing had been deleted.

EDIT According to the complete .NET code below for retrieving records:

 string strSQLStatement = "SELECT * FROM [tbl] WHERE [id] > 6;" + "DELETE FROM [tbl] WHERE [id] > 6"; using (SqlCommand cmd = new SqlCommand(strSQLStatement, connectionString)) { using (SqlDataReader rdr = cmd.ExecuteReader()) { while(rdr.Read()) { //Read values val0 = rdr.GetInt32(0); val3 = rdr.GetInt32(3); //etc. } } } 

Answer 1

This will make selection and deletion at the same time:

 delete from [tbl] output deleted.* WHERE [id] > 6 

Answer 2

You can select and delete in the same transaction as long as both operations are credited to the same transaction.

Look at this Transaction .net post

Answer 3

The easiest way to achieve transactions with a compatible provider (SQL Server works great!) Is to use TransactionScope . Just make sure the area is created before the connection is opened, so that everything is correctly enrolled.

The contents of the SelectStuff and DeleteStuff not significant - just use the same connection, do not scold the connection or transactions manually, but perform SQL operations best.

 // Notes // - Create scope OUTSIDE/BEFORE connection for automatic enlisting // - Create only ONE connection inside to avoid DTC and "advanced behavior" using (var ts = new TransactionScope()) using (var conn = CreateConnection()) { // Make sure stuff selected is MATERIALIZED: // If a LAZY type (Enumerable/Queryable) is returned and used later it // may cause access to the connection outside of when it is valid! // Use "ToList" as required to force materialization of such sequences. var selectedStuff = SelectStuff(conn); DeleteStuff(conn); // Commit ts.Complete(); // Know stuff is deleted here, and access selected stuff. return selectedStuff; } 

Answer 4

The return value from multiple SQL statements is the result of the last statement run, which in this case is DELETE . There are no rows returned with DELETE , so there is nothing to read for val0 and val3 .

Two solutions can be found here:

• Change your code to explicitly start the transaction, do SELECT , read the values, and then enter DELETE or

• SELECT to table #temp do DELETE and then SELECT from table #temp , do what you need with the rows and then DROP th.