Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello i am inserting multiple rows and updating row multiple times on same moment using stored procedure and type.
SQL
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.
Posted
Updated 17-Oct-15 10:02am
v2

1 solution

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

SET TRANSACTION
ISOLATION LEVEL
READ UNCOMMITTED;
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900