我有一个存储在存储过程中的更新查询。
我想在执行存储过程后插入更新的查询。
这是我的存储过程。
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 https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15正如@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
From @Updated结果，你可以做你想做的事情：Insert, Select, etc.
阅读以下教程以更好地理解。
https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements
Example
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
