How can I create a stored procedure of inheritance tables in SQL Server
If you're trying to mimic inheritance in MS SQL Server, you'd technically create three procedures:


RegularCustRegistration: insert a regular customer
BusinessCustRegistration: insert a business customer
CustRegistration: insert common data to Customer


NOTE: Since the ContactID in Regular_Customer and Business_Customer must match the one in Customer, you need to remove the IDENTITY attribute on those two tables. All ContactID values must be generated from Customer.

The code for CustRegistration and RegularCustRegistration would look something like this:

CREATE PROCEDURE CustRegistration
      ( @ContactID int OUTPUT
       ,@CustomerTypeId int
       ,@FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18)
       ,@Notes varchar(250)
       ,@AddDate date
      )
AS
BEGIN
    DECLARE @CustOut TABLE (ContactID int);

    INSERT INTO Customers (CustomerTypeId, FirstName, LastName, DOB, Telephone, Notes, AddDate)
    OUTPUT INSERTED.ContactID INTO @CustOut
    VALUES (@CustomerTypeId, @FirstName, @LastName, @DOB, @Telephone, @Notes, @AddDate);

    SELECT TOP (1) @ContactID = ContactID
      FROM @CustOut;
END;

CREATE PROCEDURE RegularCustRegistration
      ( @FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18) = NULL
       ,@Notes varchar(250) = NULL
       ,@AddDate date
       ,@Specification varchar(45) = NULL
       ,@Date_Joined date = NULL
      )
AS
BEGIN
    DECLARE @ContactID int;
    DECLARE @CustomerTypeID int = 1; -- regular

    EXECUTE CustRegistration
            @ContactID OUTPUT
           ,@CustomerTypeID
           ,@FirstName
           ,@LastName
           ,@DOB
           ,@Telephone
           ,@Notes
           ,@AddDate
    ;

    INSERT INTO Regular_Customer (ContactID, Specification, Date_Joined)
    VALUES (@ContactID, @Specification, @Date_Joined);
END;


See this dbfiddle.uk link to see this work. It also has corrected versions of your table creation statements (IDENTITY removed from Regular_Customer and Business_Customer; PRIMARY KEY has to come after IDENTITY(1,1); need to use SET IDENTITY_INSERT &lt;table&gt; ON to insert explicit values to an IDENTITY column).

Since we need the identity value from the Customers table to create our row in Regular_Customer, we use the OUTPUT clause of the INSERT statement to capture the ID created by the INSERT, and mark the @ContactID parameter of the CustRegistration procedure as an OUTPUT parameter, so the value put in that parameter is passed back to the specified variable in the calling procedure.

You should be able to create BusinessCustRegistration based on this.