SQLTeam.com | Weblogs | Forums

Insert into and update stock in second table

Hi,
I'm new in sql and need help. Let see my part stored procedure code.

USE [HT_V1]
GO
/****** Object:  StoredProcedure [dbo].[SAVE_EXIT_TRUM]    Script Date: 2022-07-28 11:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[SAVE_RUMAG_01_02]    Script Date: 2022-01-18 12:50:46 ******/

ALTER procedure [dbo].[SAVE_EXIT_TRUM]
	@AKT_MAG nvarchar(2),
	@SYM_DOK nvarchar(2),
	@NR_DOK nvarchar(7),
	@GEN_MAG1 nvarchar(2),
	@GEN_DOK1 nvarchar(2),
	@NR_DOK1 nvarchar(7),
	@GEN_MAG2 nvarchar(2),
	@GEN_DOK2 nvarchar(2),
	@NR_DOK2 nvarchar(7),
	@GEN_MAG3 nvarchar(2),
	@GEN_DOK3 nvarchar(2),
	@NR_DOK3 nvarchar(7),
	@ROK nvarchar(2)
			
AS
BEGIN
	BEGIN TRAN TRUM01
	declare @sql1 nvarchar(max)
	declare @sql2 nvarchar(max)
	declare @sql3 nvarchar(max)
	declare @sql4 nvarchar(max)
	declare @sql5 nvarchar(max)
	declare @sql6 nvarchar(max)
	declare @Q nvarchar(max)
	
	if @AKT_MAG <>''
		begin
		declare @RecordCount int
		SELECT  @RecordCount = count (*) from [dbo].[TR101] where [SYM_DOK] = @SYM_DOK and [NR_DOK] = @NR_DOK and [ROK] = @ROK
		set @sql1 = N' INSERT INTO [dbo].[TRUM1' + @AKT_MAG + ']
		 	([SYM_DOK],
			[NR_DOK],
			[ROK],
			[TOWAR],
			[ILOSC],
			[ILOPAK],
			[CENA],
			[CENA_SPRZE],
			[STOCK],
			[STANMAG],
			[DOK_PRZYJ],
			[DOPOZ],
			[WAZNY],
			[EAN])
		SELECT	
				@TR_SYM_DOK,
				@TR_NR_DOK,
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
				[STOCK],
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'
		exec sp_executesql @sql1,
					N'@TR_SYM_DOK nvarchar(2), @TR_NR_DOK nvarchar(7), @1ROK nvarchar(2)',
					@TR_SYM_DOK = @SYM_DOK,
					@TR_NR_DOK = @NR_DOK,
					@1ROK = @ROK								
	end

	if @GEN_MAG1 <>''
		begin
		
	set @sql2 = N' INSERT INTO [dbo].[TRUM1' + @GEN_MAG1 + ']
		 	([SYM_DOK],
			[NR_DOK],
			[ROK],
			[TOWAR],
			[ILOSC],
			[ILOPAK],
			[CENA],
			[CENA_SPRZE],
			[STOCK],
			[STANMAG],
			[DOK_PRZYJ],
			[DOPOZ],
			[WAZNY],
			[EAN])
		SELECT	
				@1SYM_DOK,
				@1NR_DOK,
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
				[STOCK],
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'
	exec sp_executesql @sql2,
					N'@TR_SYM_DOK nvarchar(2), @TR_NR_DOK nvarchar(7), @1ROK nvarchar(2),
					@1SYM_DOK nvarchar(2),  @1NR_DOK nvarchar(7)',
					@TR_SYM_DOK = @SYM_DOK,
					@TR_NR_DOK = @NR_DOK,
					@1ROK = @ROK,
					@1SYM_DOK = @GEN_DOK1,
					@1NR_DOK = @NR_DOK1
					
	end

	if @GEN_MAG2 <>''
		begin
		
	set @sql3 = N' INSERT INTO [dbo].[TRUM1' + @GEN_MAG2 + ']
		 	([SYM_DOK],
			[NR_DOK],
			[ROK],
			[TOWAR],
			[ILOSC],
			[ILOPAK],
			[CENA],
			[CENA_SPRZE],
			[STOCK],
			[STANMAG],
			[DOK_PRZYJ],
			[DOPOZ],
			[WAZNY],
			[EAN])
		SELECT	
				@2SYM_DOK,
				@2NR_DOK,
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
				[STOCK],
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'
	exec sp_executesql @sql3,
					N'@TR_SYM_DOK nvarchar(2), @TR_NR_DOK nvarchar(7), @1ROK nvarchar(2),
					@2SYM_DOK nvarchar(2),  @2NR_DOK nvarchar(7)',
					@TR_SYM_DOK = @SYM_DOK,
					@TR_NR_DOK = @NR_DOK,
					@1ROK = @ROK,
					@2SYM_DOK = @GEN_DOK2,
					@2NR_DOK = @NR_DOK2
					
	end

	if @GEN_MAG3 <>''
		begin
		
	set @sql4 = N' INSERT INTO [dbo].[TRUM1' + @GEN_MAG3 + ']
		 	([SYM_DOK],
			[NR_DOK],
			[ROK],
			[TOWAR],
			[ILOSC],
			[ILOPAK],
			[CENA],
			[CENA_SPRZE],
			[STOCK],
			[STANMAG],
			[DOK_PRZYJ],
			[DOPOZ],
			[WAZNY],
			[EAN])
		SELECT	
				@3SYM_DOK,
				@3NR_DOK,
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
				[STOCK],
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'
	exec sp_executesql @sql4,
					N'@TR_SYM_DOK nvarchar(2), @TR_NR_DOK nvarchar(7), @1ROK nvarchar(2),
					@3SYM_DOK nvarchar(2),  @3NR_DOK nvarchar(7)',
					@TR_SYM_DOK = @SYM_DOK,
					@TR_NR_DOK = @NR_DOK,
					@1ROK = @ROK,
					@3SYM_DOK = @GEN_DOK3,
					@3NR_DOK = @NR_DOK3
					
	end
	
	Select @q =
		case when @SYM_DOK in('PW', 'PZ', 'M+') then '1'
	end
	if @Q = '1'
	begin
	set @sql6 = N' INSERT INTO [dbo].[PRZYTOW]
		 	([TOWAR],
			[DOK_PRZYJ],
			[PRZYJETO],
			[EAN],
			[WAGA],
			[PACZKA],
			[WZJM],
			[KOD_PROD],
			[NR_SPIEN],
			[DOT_FIR])
		SELECT	
			[TOWAR],
			[DOK_PRZYJ],
			[ILOSC],
			[EAN],
			[WAGA],
			[PACZKA],
			@WZJM,
			[KOD_PROD],
			[NR_SPIEN],
			[DOT_FIR]				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'
		exec sp_executesql @sql6,
					N'@TR_SYM_DOK nvarchar(2), @TR_NR_DOK nvarchar(7), @1ROK nvarchar(2), @WZJM bit',
					@TR_SYM_DOK = @SYM_DOK,
					@TR_NR_DOK = @NR_DOK,
					@1ROK = @ROK,
					@WZJM = 1
	end
		
		
END

declare @RecordCount1 bigint
set @sql5 = 'select @Record = count(*) from [dbo].[TRUM1' + @AKT_MAG + '] where [SYM_DOK] = @SYM_DOK2 and [NR_DOK] = @NR_DOK2 and [ROK] = @1ROK'

exec sp_executesql @sql5,
					N'@RECORD int output, @SYM_DOK2 nvarchar(2), @1ROK nvarchar(2), @NR_DOK2 nvarchar(7)',
					@SYM_DOK2 = @SYM_DOK,
					@NR_DOK2 = @NR_DOK,
					@1ROK = @ROK,
					@RECORD = @RecordCount1 output

	if @RecordCount1 > @RecordCount
		begin
		rollback tran TRUM01
		end
	else
		begin
		commit tran TRUM01
		DELETE FROM [DBO].[TR101] WHERE [SYM_DOK] = @SYM_DOK and [NR_DOK] = @NR_DOK and [ROK] = @ROK
		end

Before I close document, all important data are stored in TR101 table.

In this specifict document i will create (@GEN_MAG1), need replace as bellow:

set @sql2 = N' INSERT INTO [dbo].[TRUM1' + @GEN_MAG1 + ']
		 	([SYM_DOK],
			[NR_DOK],
			[ROK],
			[TOWAR],
			[ILOSC],
			[ILOPAK],
			[CENA],
			[CENA_SPRZE],
			**[STOCK],**
			[STANMAG],
			[DOK_PRZYJ],
			[DOPOZ],
			[WAZNY],
			[EAN])
		SELECT	
				@1SYM_DOK,
				@1NR_DOK,
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
*(instead [STOCK]) need (existing stock in [dbo].[TRUM1' + @GEN_MAG1 + '] depend of [EAN]) + quantity,*
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = @TR_SYM_DOK and [NR_DOK] = @TR_NR_DOK and [ROK] = @1ROK'

Will be posible to do?
The TR101 table could stored 100 position of EAN and will be stored into [dbo].[TRUM1' + @GEN_MAG1 + '] at once.
Thanks in advance.

Ok.
I found solution.

use [HT_V1]
		SELECT	
				[SYM_DOK],
				[NR_DOK],
				[ROK],
				[TOWAR],
				[ILOSC],
				[ILOPAK],
				[CENA],
				[CENA_SPRZE],
				STOCK = (SELECT top 1 [STOCK] FROM [DBO].[TRUM101] WHERE [EAN]=TR101.[EAN] order by [NUMER] desc) + [ILOSC],
				[STANMAG],
				[DOK_PRZYJ],
				[DOPOZ],
				[WAZNY],
				[EAN]
				
			FROM [dbo].[TR101]
			WHERE [SYM_DOK] = 'M+' and [NR_DOK] = '010001' and [ROK] = '22'

This is just test sample but working and give me an idea.