hello i am inserting multiple rows and updating row multiple times on same moment using stored procedure and type.
USE [SMB]
GO
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.