如何更新多个数据 [英] how to update multiple datas

问题描述

你好我正在使用存储过程和类型在同一时刻插入多行并多次更新行。

hello i am inserting multiple rows and updating row multiple times on same moment using stored procedure and type.

USE [SMB]
GO
/****** Object:  StoredProcedure [dbo].[AddS_Sell]    Script Date: 10/18/2015 01:04:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[AddS_Sell]

@date datetime,
@Total decimal(18,2),
@Add_Sell [dbo].[AddS_Sell] READONLY
AS

BEGIN

declare @ScopeIdentity uniqueidentifier; 
 set @ScopeIdentity= NEWID();
 
	  SET NOCOUNT ON;
	  begin try
	  begin transaction
insert into [SMB].[dbo].[S_SellMaster]([Id],[Date],[Total])values(@ScopeIdentity,@date,@Total);
insert into [SMB].[dbo].[S_SellDetails] ([Party],[Quality],[Quantity],[Rate],[Labour],[Net],[Sid])select 
[Party],[Quality],[Quantity],[Rate],[Labour],[NetAmt],@ScopeIdentity
from @Add_Sell;

update  b set b.[Stock]= b.[Stock]-c.[Quantity] from   @Add_Sell  as c inner join [SMB].[dbo].[S_Quality] as b  on b.Id=c.[Quality]
where b.[Id]=@ScopeIdentity;
update  b set b.[Stock]=b.[Stock]-c.[Quantity] from   @Add_Sell  as c inner join [SMB].[dbo].[S_Quality] as b 
  on c.[Quality]=b.[Id] ;
	  
      commit transaction
		end try
		BEGIN CATCH
		rollback transaction;
		declare @ErrorMessage nvarchar(max), @ErrorSeverity 
int, @ErrorState int;

    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast

(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), 

@ErrorState = ERROR_STATE();

    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);

		END CATCH

END

多次插入工作正常。但是在更新多行时只有第一行数据在另一个表中更新。

multiple insertion is working properly. but while updating multiple rows only first rows data is updated in another table.

推荐答案

请在BEGIN之后添加以下行（行号：8）：



套餐交易

隔离级别

无法解读;

Please add below line after BEGIN(line no:8):

SET TRANSACTION
ISOLATION LEVEL
READ UNCOMMITTED;

这篇关于如何更新多个数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！