Sql-server – Get the last ID from Multiple Stored Procedures Executed

sql serverxml

So I have an XML file , to take data from it and insert into tables.
For this I’ve created 3 procedures(INSERTXMLMultipleTables,INSERTXMLNIAC,INSERTNIACFORCOMPONENTS).

The logic of these procedure is that the first two just insert simple data into table, have an unique ID, the last one (INSERTNIACFORCOMPONENTS) works like this(INSERTNIACFORCOMPONENTS->INSERTXMLMultipleTables+INSERTXMLNIAC).

INSERTNIACFORCOMPONENTS just inserts all the ids from other tables into NIACtoComponents table, so they could be linked to NIAC.

So, with these 3 procedures I insert data just from one NIAC node.

Now, I have to do this task, but for all NIAC nodes(suppose there are 50-70 NIACs).
For this I've creted the last SP(InsertforNIACList)
I’ve tried to count the number of NIACs inside the NIACList, so then I could insert them into the last table(NIACList).

I’ve wanted to retrieve the id(idniac) from the NIACFORCOMPONENTS using scope_identity(), to use it in insertion for the NIACList table, but it failed, and I don’t know why.
Bellow I will attach my SP code to understand better the situation:

CREATE procedure [dbo].[InsertXMLMultipleTables]
(
@idAddress1 int out,
@idAddress2 int out,
@idactivities int out,
@idgoods int out,
@idmobileunit int out,
@idcommercial int out,
@idcommercialut int out,
@idpsu int out,
@idmerchant int out,
@xml xml
)
as 


begin 
set nocount on

INSERT INTO Activities(Code,Name)
        SELECT  
    
Code=c.value('Code[1]','nvarchar(90)') ,
Name=c.value('Name[1]','nvarchar(90)') 
FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/Activities/Activity') Activities(c)
set @IDActivities=SCOPE_IDENTITY();
end;




begin
INSERT INTO Address(Region,Locality,Street,House,Block,Flat,Phone,Fax,Email)
        SELECT 
        Region=c.value('Region[1],','nvarchar(60)'),
        Locality=c.value('Locality[1],','nvarchar(50)') ,
        Street=c.value('Street[1],','nvarchar(60)') ,
        House=c.value('House[1],','nvarchar(10)') ,
        Block=c.value('Block[1],','nvarchar(10)') ,
        Flat=c.value('Flat[1],','nvarchar(10)') ,
        Phone=c.value('Phone[1],','nvarchar(30)') ,
        Fax=c.value('Fax[1],','nvarchar(60)') ,
        Email=c.value('Email[1],','nvarchar(60)') 
    FROM @xml.nodes('NIACList/NIAC/Merchant/Address') Address(c)
    set @idAddress1=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO Merchant(IDNX,Name,WorkProgram,IdAddress)
        SELECT 
        Region=c.value('IDNx[1],','nvarchar(max)'),
        Locality=c.value('Name[1],','nvarchar(max)') ,
        Street=c.value('WorkProgram[1],','datetime2') ,
        @idAddress1
    FROM @xml.nodes('NIACList/NIAC/Merchant') Merchant(c)
    set @idmerchant=SCOPE_IDENTITY();
    end;

    
begin
INSERT INTO Address(Region,Locality,Street,House,Block,Flat,Phone,Fax,Email)
        SELECT 
        Region=c.value('Region[1],','nvarchar(60)'),
        Locality=c.value('Locality[1],','nvarchar(50)') ,
        Street=c.value('Street[1],','nvarchar(60)') ,
        House=c.value('House[1],','nvarchar(10)') ,
        Block=c.value('Block[1],','nvarchar(10)') ,
        Flat=c.value('Flat[1],','nvarchar(10)') ,
        Phone=c.value('Phone[1],','nvarchar(30)') ,
        Fax=c.value('Fax[1],','nvarchar(60)') ,
        Email=c.value('Email[1],','nvarchar(60)')   
    FROM @xml.nodes('NIACList/NIAC/CommercialUnit/Address') Address(c)
    set @idAddress2=SCOPE_IDENTITY();
    set @idcommercialut=SCOPE_IDENTITY();

    end;


    begin
    INSERT INTO CommercialApparatus(Count,Length,Width,Height )
        SELECT      
    Count =c.value('Type[1]','int') ,
        Length=c.value('Length[1]','int') ,
         Width=c.value('Width[1]','int') ,
         Height=c.value('Height[1]','int') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/CommercialApparatus') CommercialApparatus(c)
    set @idcommercial=SCOPE_IDENTITY();
    end;




    begin
    INSERT INTO Goods(Name)
        SELECT 
        Name=c.value('Name[1]','nvarchar(60)') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/Goods/Good') Goods(c)
    set @idgoods=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO MobileUnit(Type,Length,Width,Height )
        SELECT  
    Type =c.value('Type[1]','int') ,
        Length=c.value('Length[1]','int') ,
         Width=c.value('Width[1]','int') ,
         Height=c.value('Height[1]','int') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/MobileUnit') MobileUnit(c)
         set @idmobileunit=SCOPE_IDENTITY();
    end;



    begin
    INSERT INTO PublicSupplyUnit(Capacity,TerraceCapacity)
        SELECT 
        Capacity=c.value('Capacity[1]','int') ,
        TerraceCapacity=c.value('TerraceCapacity[1]','int')
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/PublicSupplyUnit') PublicSupplyUnit(c)
    set @idpsu=SCOPE_IDENTITY();
    end;



    begin
    INSERT INTO CommercialUnit(IDNX,Name,Type,Area,Location,TerraceCapacity,TradingAlcohol,TradingBeer,TradingTobaccoProducts,AmbulatoryTrading,MobileUnitTrading,CommercialApparatusTrading,IDActivities,IdAddress,IDCommercial,IDGoods,IDMobileUnit,IDPSU)
        SELECT 
        IDNx=c.value('IDNx[1],','nvarchar(90)'),
        Name=c.value('Name[1],','nvarchar(90)') ,
        Type=c.value('Type[1],','nvarchar(90)') ,
        Area=c.value('Area[1],','int') ,
        Location=c.value('Location[1],','nvarchar(max)'),
        TerraceCapacity=c.value('TerraceCapacity[1],','float') ,
        TradingAlcohol=c.value('TradingAlcohol[1],','bit') ,
        TradingBeer=c.value('TradingBeer[1],','bit') ,
        TradingTobaccoProducts=c.value('TradingTobaccoProducts[1],','bit'),
        AmbulatoryTrading=c.value('AmbulatoryTrading[1],','bit') ,
        MobileUnitTrading=c.value('MobileUnitTrading[1],','bit') ,
        CommercialApparatusTrading=c.value('CommercialApparatusTrading[1],','bit') ,
        @idactivities,@idAddress2,@idcommercial,@idgoods,@idmobileunit,@idpsu
    FROM @xml.nodes('NIACList/NIAC/CommercialUnit') CommercialUnit(c)
    end;


GO

CREATE procedure [dbo].[InsertXMLNIAC]
(
@xml xml,
@idNIAC int output,
@idCessation int output
)
as 

begin 
set nocount on


INSERT INTO NIAC(Number,SubmissionDate,ExpirationDate,IssuerIDNO,IssuerName,SuspensionPeriod,Modifications)
        SELECT 
        Number=c.value('Number[1],','nvarchar(80)'),
        SubmissionDate=c.value('SubmissionDate[1],','datetime2') ,
        ExpirationDate=c.value('ExpirationDate[1],','datetime2') ,
        IssuerIDNO=c.value('IssuerIDNO[1],','nvarchar(max)') ,
        IssuerName=c.value('IssuerName[1],','nvarchar(60)') ,
        SuspensionPeriod=c.value('SuspensionPeriod[1],','datetime2') ,
        Modifications=c.value('Modifications[1],','nvarchar(60)') 
    FROM @xml.nodes('NIACList/NIAC') NIAC(c)
    set @idNIAC=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO Cessation(Basis,Date,IDNIAC)
        SELECT 
        Basis=c.value('Basis[1],','nvarchar(60)'),
        Date=c.value('Date[1],','date') ,      
        @idNIAC
    FROM @xml.nodes('NIACList/NIAC/Cessation') Cessation(c)
    set @idCessation=SCOPE_IDENTITY();
    end;

GO


CREATE procedure [dbo].[NIACFORCOMPONENTS]
@xml xml,
@idNIAC int out
        

AS
BEGIN
declare
    
        @idCessation int ,
        @idmerchant int ,
        @idAddress1 int ,
        @idAddress2 int,
        @idcommercialut int,
        @idactivities int,
        @idgoods int,
        @idmobileunit int,
        @idcommercial int,
        @idpsu int


    SET NOCOUNT ON;
    
    declare @trancount int;
    set @trancount=@@TRANCOUNT;
    BEGIN TRANSACTION

    EXEC [dbo].[InsertXMLMultipleTables]
        @idAddress1 = @idAddress1 OUTPUT,
        @idAddress2 = @idAddress2 OUTPUT,
        @IDActivities = @idactivities OUTPUT,
        @idgoods = @idgoods OUTPUT,
        @idmobileunit = @idmobileunit OUTPUT,
        @idcommercial = @idcommercial OUTPUT,
        @idcommercialut = @idcommercialut OUTPUT,
        @idpsu = @idpsu OUTPUT,
        @idmerchant = @idmerchant OUTPUT,
        @xml = @xml


        EXEC [dbo].[InsertXMLNIAC]
        
        @idNIAC = @idNIAC OUTPUT,
        @idCessation = @idCessation OUTPUT,
        @xml=@xml

     
    
         begin try
        if @trancount = 0
            begin transaction
        else
            save transaction NIACFORCOMPONENTS;

        

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
                 @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction NIACFORCOMPONENTS;

        raiserror ('NIACFORCOMPONENTS: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
    
    Begin
                              
        INSERT INTO NIACToComponents(IDNIAC,IdCessation,IdMerchant,IDAddressMerchant,IDAddressCU,IDCommercialUt,IDActivities,IDGoods,IDMobileUnit,IDCommercial,IDPSU)
        SELECT 
        @idNIAC as N'@idNIAC',
        @idCessation as N'@idCessation',
        @idmerchant as N'@idmerchant',
        @idAddress1 as N'@idAddress1',
        @idAddress2 as N'@idAddress2',
        @idcommercialut as N'@idcommercialut',
        @idactivities as N'@idactivities',
        @idgoods as N'@idgoods',
        @idmobileunit as N'@idmobileunit',
        @idcommercial as N'@idcommercial',
        @idpsu as N'@idpsu'

    COMMIT

    
        END             
END


CREATE procedure [dbo].[InsertforNIACLIST]
@xml xml,
@idNIAC int out 
as
begin
EXEC [dbo].[NIACFORCOMPONENTS]
@xml = @xml,
@idniac=@idniac output

set @idniac=SCOPE_IDENTITY();



DECLARE @cnt INT, @i INT;

SET @cnt = @xml.value('count(NIACList/NIAC)', 'INT');

SET @i = 1;
WHILE @i <= @cnt
BEGIN

INSERT INTO NIACList(IDNIAC)
    SELECT 
    @idNIAC
    FROM @xml.nodes('NIACList/NIAC[sql:variable("@i")]') AS NIACList(c)
   SET @i += 1;

END

select *from NIACList

END

UPDATE

So i've tried differently, and I've could do the count process of NIACs inside NIACList using table variables, and now I want to use that code into an INSERT scope, how can I do this?

Bellow is the code for the update:

alter procedure [dbo].[InsertforNIACLIST]
@xml xml,
@idNIAC int out 
as
begin
EXEC [dbo].[NIACFORCOMPONENTS]
@xml=@xml,
@idniac=@idNIAC output

DECLARE @temp TABLE (xml xml);



INSERT @temp VALUES (@xml);


WITH NIACList AS
(
    SELECT n.value('local-name(.)', 'nvarchar(256)') IDNIAC
        FROM @temp t
        CROSS APPLY t.xml.nodes('/NIACList/*') x(n)
)
SELECT IDNIAC, count(*) cnt
    FROM NIACList
    GROUP BY IDNIAC;

Best Answer

You can use OUTPUT to solve this kind of problem the example below

USE AdventureWorks2012;
GO

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,  
    Name VARCHAR(50),  
    ModifiedDate DATETIME); 
    
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  
  
--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;  
GO
Related Question