sql - 更新具有不同 id 的两个表的存储过程

Question

我有这样的存储过程:

alter procedure [dbo].[delivary] @dedate nvarchar(100), 
                                 @carid nvarchar(100), 
                                 @transid integer as
begin
    select t.transactID 
      from Transaction_tbl t 
     where t.TBarcode = @carid
    update Transaction_tbl 
       set DelDate = '' + @dedate + '', Status=5 
     where TBarcode = @carid
    update KHanger_tbl 
       set Delivered=1 
     where transactid=@transid
end

我可以更新我的交易表。我还想用匹配 @carid 的 TransactID 更新表 KHanger_table。

我该怎么做？

Answer 1

有两种方法可以做到:

首先检索您的 transactID 并将其存储在变量中:

alter procedure [dbo].[delivary] @dedate nvarchar(100), 
                                 @carid nvarchar(100)as
begin
    declare @transid int
    select @transid = t.transactID 
      from Transaction_tbl t 
     where t.TBarcode = @carid

    update Transaction_tbl 
       set DelDate = '' + @dedate + '', Status=5 
     where TBarcode = @carid

    update KHanger_tbl 
       set Delivered=1 
     where transactid=@transid
end

你有关系更新:

alter procedure [dbo].[delivary] @dedate nvarchar(100), 
                                 @carid nvarchar(100) as
begin
    update Transaction_tbl 
       set DelDate = '' + @dedate + '', Status=5 
     where TBarcode = @carid

    update KHt
      set KHt.Delivered=1
    from KHanger_tbl as KHt
      inner join Transaction_tbl t
        on KHt.transactionid = t.transactID
    where t.TBarcode = @carid
end