How to run multiple queries in one instance in SQL Server 2005

Question

I want to run a SELECT statement and I want execute a DELETE statement for the same row and the read result be respond by the SQL Server.

Answer 1

WITH cte AS (
SELECT * 
FROM <mytable>
WHERE key = <mykey>)
DELETE cte
OUTPUT deleted.*;

There are many ways to skin this cat. I often preffer the one posted because is very readable. It clearly separates the SELECT into its own query expression, allowing for easily created complex queries. It deletes exactly the query result. It outputs the deleted rows.

The following is also perfectly valid and easier for simple WHERE clauses:

DELETE <mytable>
OUTPUT deleted.*
WHERE key = <mykey>;