需要SQL查询 - 在单个StoredProcedure中选择和删除 [英] Need SQL Query - Select and Delete in Single StoredProcedure

问题描述

我想根据某些条件从一张表中选择记录，表格数据将被清除。它将在单一存储中完成Procedure.But它因为删除查询而返回空的数据表。我该如何实现呢？



例如。我的查询是这样的，



从table1中选择*其中id = 1

从table1中删除id = 1



如何获取DAL中的记录以及要在表格中删除的数据。





感谢提前..

Hi,

I want to select the records from one table based on some condition and that table data will be cleared.It is to be done in Single Stored Procedure.But it returns empty datatable because of delete query.How can i implement this?

Eg. My query is like this,

Select * from table1 where id = 1
Delete from table1 where id = 1

How can i get the records in DAL and as well as datas to be deleted in table.


Thanks in Advance..

推荐答案

可能最简单的方法是选择一个临时表，然后删除查询临时表以获取SP输出例如

Probably the easiest way is to select into a temporary table, then do the delete the query the temporary table to get the SP output e.g.

create procedure sp_vksvpp
as
begin
    Select * into #temp from table1 where id = 1
    Delete from table1 where id = 1
    select * from #temp
end
go

或者您可以使用After Delete触发器执行某些操作，但这可能是矫枉过正

Alternatively you could do something with a After Delete trigger but that is probably overkill

这篇关于需要SQL查询 - 在单个StoredProcedure中选择和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！