USE [Database]

IF OBJECT_ID('[Procedures].[delete_product_type]', 'P') IS NOT NULL
  DROP PROCEDURE [Procedures].[delete_product_type];
GO
CREATE PROCEDURE [Procedures].[delete_product_type]
  @product_type_id AS INT
AS
  DECLARE @newParentId INT;
  SET @newParentId = (
    SELECT [parent_id]
    FROM [Tables].[product_types]
	WHERE [id] = @product_type_id
  )
  BEGIN TRANSACTION;
  UPDATE [Tables].[product_types]
  SET [parent_id] = @newParentId
  WHERE [parent_id] = @product_type_id

  DELETE FROM [Tables].[product_types]
  WHERE [id] = @product_type_id;
  COMMIT TRANSACTION;
GO