USE [Database]

IF OBJECT_ID('[Procedures].[insert_product_type]', 'P') IS NOT NULL
  DROP PROCEDURE [Procedures].[insert_product_type];
GO
CREATE PROCEDURE [Procedures].[insert_product_type]
  @parent_id                AS INT = NULL,
  @name                     AS NVARCHAR(128),
  @inserted_product_type_id AS INT = NULL OUTPUT
AS
  BEGIN TRY
    INSERT INTO [Tables].[product_types]([parent_id], [name])
    VALUES(@parent_id, @name);
	SET @inserted_product_type_id = SCOPE_IDENTITY();
  END TRY
  BEGIN CATCH
      DECLARE @message NVARCHAR(128);
      SET @message =
      'Cannot create [a] subgroup. Cause: the group '
      + '(name: "'+ (SELECT [name] FROM [Tables].[product_types] WHERE [id] = @parent_id) +'"; [id]:'+ LTRIM(STR(@parent_id)) +')'
      + ' is referenced by [a] product object.';
    PRINT 'Actual error number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
      PRINT 'Actual line number: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
      THROW 51000, @message, 1;
  END CATCH
GO