How to call an insert stored procedure during another insert stored procedure and get its returned ID?

Question

My apologies if my question doesn't make sense.

So I have taken a database table that uses GUID's as the PK's and I have normalized this database and am starting to merge the data from the existing database to the newly structured database. I have an originating table that looks like this

create table #tmpMaterial
(
    ID int identity(1,1),
    MaterialID nvarchar(128),
    MaterialCategoryID nvarchar(128),
    MaterialTypeID nvarchar(128),
    MaterialSubTypeID nvarchar(128),
    MaterialDetail nvarchar(20),
    Description nvarchar(100),
    MaterialWidth numeric(10,2),
    MaterialLength int,
    MaterialSize nvarchar(20),
    PurchUnitOfMeasureID nvarchar(128),
    PurchaseItemQuantity int,
    SellUnitOfMeasureID nvarchar(128),
    SellItemQuantity int,
    NewPrice money,
    RemodPrice money,
    ServicePrice money,
    DefVendorID nvarchar(128),
    EnabledInd tinyint
)

and the inserting of the data is this

insert into #tmpMaterial
select tmpM.MaterialID, tmpM.MaterialCategoryID,tmpM.MaterialTypeID, tmpM.MaterialSubTypeID, tmpM.MaterialDetail, tmpM.Description, tmpM.MaterialWidth, tmpM.MaterialLength, tmpM.MaterialSize,
tmpM.PurchUnitOfMeasureID, tmpM.PurchItemQuantity, tmpM.SellUnitOfMeasureID, tmpM.SellItemQuantity, tmpM.NewPrice, tmpM.RemodPrice, tmpM.ServicePrice, tmpM.DefVendorID, tmpM.EnabledInd
from Exovations.dbo.Material tmpM

So this inserts the original data into the temp table, this temp table is what I am querying against. I have a temp table which is the exact newly created table and it is

create table #tmpMaterialFinal
(
    ID int identity(1,1),
    MaterialCategoryID int,
    MaterialTypeID int,
    MaterialSubTypeID int,
    CompanyID int,
    DimensionsID int,
    PurchaseUOMID int,
    SellUOMID int,
    VendorID int,
    MaterialDetail nvarchar(20),
    Description nvarchar(100),
    PurchaseItemQuantity int,
    NewPrice money,
    RemodelPrice money,
    ServicePrice money,
    IsActive bit
)

I am querying the #tmpMaterial table and doing some joins in the process to import that data into the #tmpMaterialFinal table. Where I am forseeing an issue is if you take a look at the #tmpMaterial table I have these three columns

MaterialWidth numeric(10,2),
MaterialLength int,
MaterialSize nvarchar(20)

and in my #tmpMaterialFinal I have DimensionsID, which is its own table called Dimensions, in the Dimensions table I have 3 columns, one for Width, Length and Size.

What I need to do is while inserting records into the #tmpMaterialFinal table I need to call a stored procedure called usp_InsertDimensions and pass those 3 values from the #tmpMaterial table and while inserting into the #tmpMaterialFinal table I need to execute the usp_InsertDimensions stored procedure and get its returned ID so I can place that ID into the DimensionsID column.

my usp_InsertDimensions looks like this

CREATE PROCEDURE [dbo].[usp_InsertDimensions]
(
    @DimensionID int output,
    @DLength decimal(15,4),
    @DWidth decimal(15,4),
    @DHeight decimal(15,4)
)
AS
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @ERROR_SEVERITY int,
        @MESSAGE varchar(1000),
        @ERROR_NUMBER int,
        @ERROR_PROCEDURE nvarchar(200),
        @ERROR_LINE int,
        @ERROR_MESSAGE nvarchar(4000);

begin try
     insert into [Dimensions]
     (DLength, DWidth, DHeight)
     values
     (@DLength, @DWidth, @DHeight)
     set @DimensionID = SCOPE_IDENTITY()
end try
BEGIN CATCH
    SET @ERROR_SEVERITY = ISNULL(ERROR_SEVERITY(),'');
    SET @ERROR_NUMBER = ISNULL(ERROR_NUMBER(),'');
    SET @ERROR_PROCEDURE = ISNULL(ERROR_PROCEDURE(),''); 
    SET @ERROR_LINE = ISNULL(ERROR_LINE(),'');
    SET @ERROR_MESSAGE = ISNULL(ERROR_MESSAGE(),'');

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
        BEGIN
            --PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;

    -- Test if the transaction is active and valid.
    IF (XACT_STATE()) = 1
        BEGIN
            --PRINT N'The transaction is committable. Committing transaction.'
            COMMIT TRANSACTION;   
        END;

    SET @MESSAGE = 'Error Occured in Stored Procedure ' + cast(@ERROR_PROCEDURE as varchar(200)) + 
                    '; Line Number ' + cast(@ERROR_LINE as varchar) + 
                    '; Message: [' + cast(@ERROR_NUMBER as varchar) + '] - '
                    + cast(@ERROR_MESSAGE as varchar(255))

    RAISERROR(@MESSAGE, @ERROR_SEVERITY, 1);
END CATCH;

Any ideas on the correct way to achieve this?

Answer 1

Here's one approach you could use:

1. Add a DimensionId field on your first table #tmpMaterial

2. Generate sql statements to insert dimensions into Dimensions table and store the DimensionId back onto the #tmpMaterial table. Something like this should work (don't have MSSQL at hand to test at the moment):

   select 'declare @d_id int; exec usp_InsertDimensions @d_id output, ' + cast(MaterialLength as varchar(100)) + ', ' + cast(MaterialWidth as varchar(100)) + ', ' + cast(MaterialSize as varchar(100)) + '; Update #tmpMaterial set DimensionId = @d_id where ID = ' + cast(ID as varchar(100)) + '; go;' from #tmpMaterial

This will give you all the statements required to insert the dimensions and store their ids back in your source table. You could also concatenate all these statements in a variable and then execute them as dynamic sql, or store the statements in a temp table and execute in batches.

From here on, the insert into the final table would be straightforward.