How can I create a stored procedure of inheritance tables in SQL Server I have three tables

create table CustomerTypes(
CustomerTypeID int primary key identity(1,1),
CustomerType varchar(11)
);

insert into CustomerTypes(CustomerTypeID,CustomerType)
select 1, 'Regular' union all
select 2, 'Business'

create table Customers(
ContactID int primary key identity(1,1),
CustomerTypeID int references CustomerTypes(CustomerTypeID),
FirstName varchar(25)not null,
LastName varchar(25)not null,
DOB date not null,
Telephone varchar(18),
Notes varchar(250),
AddDate date not null
constraint cust_AltPK unique (ContactID,CustomerTypeID)
);

create table Regular_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 1 persisted, --regular
Specification varchar(45),
Date_Joined date,
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);

create table Business_Customer(
ContactID int primary key identity(1,1),
CustomerTypeID as 2 persisted, --Business
Business_Name varchar(30)not null,
Business_Type varchar(30),
foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)
);


I want to create a stored procedure called CustRegistration to insert data into the various customer tables, by combining customerType table and Customer table.
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.