USE [Database]

IF OBJECT_ID('[Procedures].[insert_product]', 'P') IS NOT NULL
  DROP PROCEDURE [Procedures].[insert_product];
GO
CREATE PROCEDURE [Procedures].[insert_product]
  @name                 AS NVARCHAR(128),
  @product_type_id      AS INT,
  @barcode_attribute_id AS INT,
  @inserted_product_id  AS INT = NULL OUTPUT
AS
  BEGIN TRANSACTION;
    INSERT INTO [Tables].[products]([barcode], [name], [product_type_id])
    VALUES(@name, @product_type_id);
    SET @inserted_product_id = SCOPE_IDENTITY();
    
    INSERT INTO [Tables].[product_attributes]([product_id], [attribute_id])
    VALUES (@inserted_product_id, @barcode_attribute_id);
  COMMIT TRANSACTION;
GO