在SQL Server更新查询后插入

debugcn 发表于 Dev

约翰·欧文

我有一个存储在存储过程中的更新查询。

我想在执行存储过程后立即插入更新的查询。这是我的存储过程。

ALTER PROCEDURE [dbo].[sp_assign_account]
    @id_agency int,
    @month nvarchar(2),
    @regional nvarchar(20),
    @top int,
    @assigned_by nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [AMAS].[dbo].[tbl_sample] 
    SET id_agency = @id_agency, 
        status = 'assigned', 
        stage = 'STA', 
        tgl_assign = GETDATE(), 
        assigned_by = @assigned_by  
    WHERE id_sample IN (SELECT  TOP (@top) a.id_sample 
                        FROM [AMAS].[dbo].[tbl_sample] a
                        LEFT JOIN mysystem.lapkeu.dbo.groupbranch b ON a.branch_id = b.BranchID
                        WHERE a.is_delete = '0' 
                          AND a.status = 'not assigned' 
                          AND a.stage = 'AMA' 
                          AND MONTH(a.insert_at) = @month
                          AND a.branch_id IN (SELECT branch_id 
                                              FROM mysystem.lapkeu.dbo.groupbranch 
                                              WHERE GroupBranchID IN (SELECT b.group_branch_id 
                                                                      FROM [AMAS].[dbo].[tbl_collector_agency] a
                                                                      JOIN [AMAS].[dbo].[tbl_area_collector] b ON a.id_collector = b.id_collector
                                                                      WHERE a.id_agency = @id_agency)
        ) 
        ORDER BY NEWID()
    )

    // can i put insert query here??
END

还是可以获取更新的ID？因为我使用随机数据进行更新，所以在更新之前不初始化ID。

丰

您可以使用Output clause@Dale K的注释来实现

DECLARE @Updated table(id_agency int, .. // Any property as you want)

UPDATE [AMAS].[dbo].[tbl_sample] SET id_agency = @id_agency, status = 'assigned', stage='STA', tgl_assign = getdate(), assigned_by = @assigned_by  
OUTPUT deleted.id_agency // Any property as you want
INTO @Updated
WHERE id_sample 
IN (////)

SELECT * FROM @Updated

从@Updated结果中，您可以DoSomething的，如你所愿：Insert，Select，等。

阅读以下教程以更好地理解。

https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements

例