Reseed identity increments SQL Server C# row_number() DENSE_RANK()

Question

I am trying to insert using a stored procedure into a table where I have two primary key columns AgencyID and ModelID (int auto). The condition is that as long as the AgencyID does not change, the ModelID column will be incremented by 1 automatically.

So far no issues, but I need to reseed the ModelID back to 1 once the user changes the AgencyID.

Like this:

AgencyID  ModelID
-----------------
 AB        1
 AB        2
 AB        3
 CD        1
 CD        2
 EG        1
 EG        2
 AB        4

This is my insert stored procedure:

ALTER PROCEDURE [dbo].[spModel_insert]
     (@AgencyID varchar(10),
      @ModelName varchar(100),
      @Gender bit,
      @Email varchar(100),
      @Address varchar(250),
      @ImageURL varchar(250),
      @ModelID int output)
AS
    INSERT INTO ModelsT (AgencyID, ModelName, Gender, Email, [Address], ImageURL)
    VALUES (@AgencyID, @ModelName,  @Gender, @Email, @Address, @ImageURL)

    SET @ModelID = SCOPE_IDENTITY()

I could solve it through twisted ways in C# by using select Max Modelid through ExecuteScalar into a variable and add 1 in the filtered SQL, and dataBindingSource.Count for the first record:

int x;
x = ModelBindingSource.Count;\\ This code is inside the Add button.

if (x <= 1)
{
    txt_ModelID.Text = x.ToString();   
}
else if (x > 1)
{
    GetModelID(); // This is the max select SQL method where other variables 
    //will +1 to set it in the txt_ModelID.Text.
}
   

But the above means that the ModelID will not be auto in the stored procedure while it is needed to be auto. Also I don't find this really practical since I am trying to keep all my logic separated focusing on the stored procedures.

I know it can be done through row_number() or dense_rank() but not sure how to use it in the insert stored procedure? Please note that in case of deletion the ModelID must not be reused and the sequence must continue.

Any help?

Thanks in advance :)