id obtained through output in an insert query needs to be passed to another insertquery

Question

Until this line

EXEC sp_executesql @InsertQuery, N'@product_id BIGINT OUTPUT', @product_id OUTPUT

I managed to obtain the product_id correctly, the problem is when inserting in the @InsertQuery2, it inserts product_id = NULL

DECLARE @InsertQuery NVARCHAR(MAX)

    SET @InsertQuery = '
        INSERT INTO ' + QUOTENAME(@bdEmpresa) + '.dbo.Producto (
            producto_codigo,
            producto_codigoanterior,
            producto_status
        )
        OUTPUT INSERTED.producto_id
        SELECT 
            producto_codigo,
            producto_codigoanterior,
            producto_status
        FROM #TempProducto'

    -- Ejecutar la consulta de inserción y capturar el producto_id generado
    EXEC sp_executesql @InsertQuery, N'@producto_id BIGINT OUTPUT', @producto_id OUTPUT

    -- Insertar datos de proveedores en la tabla de la base de datos de la empresa
    DECLARE @InsertQuery2 NVARCHAR(MAX)

    SET @InsertQuery2 = '
        INSERT INTO ' + QUOTENAME(@bdEmpresa) + '.dbo.ProductoProveedor (
            producto_id,
            proveedor_ruc,
            proveedor_dscto
        )
        SELECT 
            @producto_id,
            proveedor_ruc,
            proveedor_dscto
        FROM #TempProveedores'

    -- Ejecutar la consulta de inserción
    EXEC sp_executesql @InsertQuery2, N'@producto_id BIGINT', @producto_id

I need to pass the id obtained after registration in the Product table to @InsertQuery2 since I need to insert it there too

Answer 1

OUTPUT returns a whole resultset. To capture and reuse it in another query you need to add INTO someTable.

Also, instead of injecting the database name, you can construct a variable containing SomeDb.sys.sp_executesql and then do EXEC @proc

DECLARE @InsertQuery NVARCHAR(MAX) = '

DECLARE @ids TABLE (id bigint NOT NULL);

INSERT INTO dbo.Producto (
    producto_codigo,
    producto_codigoanterior,
    producto_status
)
OUTPUT INSERTED.producto_id
INTO @ids (id)
SELECT 
    producto_codigo,
    producto_codigoanterior,
    producto_status
FROM #TempProducto;

INSERT INTO dbo.ProductoProveedor (
    producto_id,
    proveedor_ruc,
    proveedor_dscto
)
SELECT 
    i.id,
    p.proveedor_ruc,
    p.proveedor_dscto
FROM #TempProveedores p
CROSS JOIN @ids AS i;    -- shouldn''t this have some kind of join condition
';

DECLARE @proc nvarchar(1000) = QUOTENAME(@bdEmpresa) + '.sys.sp_executesql';

EXEC @proc @InsertQuery;

If you are using temp tables just to pass data between procedures then you should consider Table Types and Table Valued Parameters, as these are better designed to do that.