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.