Adding suffix to a column value for duplicate names in SQL

Scenario

Consider a school teaches multiple classes. If the students limits for a particular class(for eg “Marketing Management” )is reached, then new class should be created with same class name but with suffix i.e. (Marketing Management A”.

My tables

I created a table Schoolclass with 4 columns. We need to check with a class name already exists in column “Name “, if so return name with suffix else return name.

CREATE TABLE SchoolClass(
[Class_id] [int] NOT NULL,
[Location] [char](10) NOT NULL,
[Name] [varchar](50) NOT NULL,
MaxStudents[int] NULL,
CONSTRAINT [SchoolClass_PK] PRIMARY KEY CLUSTERED
(
[Class_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Insert script:

After that I inserted few records using the below insert script.

insert into SchoolClass values
(1, 'Bangalore', 'Marketing Management',25),
(2, 'Chennai', 'Marketing Management',25),
(3, 'Chennai', 'Online Marketing',25),
(4, 'Bangalore', 'Online Marketing',25)

My stored Procedure:

To achieve this, I am using charindex() function in SQL server.

Create PROC [dbo].[asp_CheckClassNameExists]
@ClassName as varchar(500)
,@DestinationCode as char(10)
,@NewClassName varchar(500) OUT
AS
SET NOCOUNT ON
BEGIN
Declare @Count as integer
declare @suffix char(26)

--setting suffix
select @suffix = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @Count= COUNT(*) from SchoolClass
where charindex(@classname,name) !=0
and Location=@DestinationCode

--if no records found return classname
if @Count=0
set @NewClassName=@classname

--else return classname with suffix
else
select @NewClassName=@classname + ' '+ SUBSTRING(@suffix,@count,1)
End
END

Verifying the stored proc:

declare @NewClassName varchar(500)
exec [asp_CheckClassNameExists] 'Online Marketing','Pune',@NewClassName OUT
select @NewClassName as classname

Output-Online Marketing
declare @NewClassName varchar(500)
exec [asp_CheckClassNameExists] 'Online Marketing','bangalore',@NewClassName OUT
select @NewClassName as classname

Output-Online Marketing A
declare @NewClassName varchar(500)
exec [asp_CheckClassNameExists] 'Marketing Management','bangalore',@NewClassName OUT
select @NewClassName as classname

Output- Marketing Marketing A