USE [Database]

IF OBJECT_ID('[Procedures].[specify_product_type]', 'P') IS NOT NULL
  DROP PROCEDURE [Procedures].[specify_product_type];
GO
CREATE PROCEDURE [Procedures].[specify_product_type]
  @parent_id INT = NULL,
  @name NVARCHAR(128),
  @inserted_product_type_id AS INT = NULL OUTPUT
AS
  BEGIN TRANSACTION;
  SET NOCOUNT ON;
  
  INSERT INTO [Tables].[product_types]([parent_id], [name])
  VALUES(DEFAULT, @name);
  SET @inserted_product_type_id = SCOPE_IDENTITY();

  UPDATE [Tables].[products]
  SET [product_type_id] = @inserted_product_type_id
  WHERE [product_type_id] = @parent_id;
  
  UPDATE [Tables].[product_types]
  SET [parent_id] = @parent_id
  WHERE [id] = @inserted_product_type_id

  COMMIT TRANSACTION;
GO
