Auto Increment Id in stored procedure not working

Auto Increment Id in stored procedure not working

I am trying to get company id like "Cp-00001". If data exists in table
then the id should be "Cp-00001" + 1 = "Cp=00002" and do on...
Here's what I have so far:
CREATE PROCEDURE [dbo].[sp_AutoGenerateCustomerCode]
AS
DECLARE @id VARCHAR(10)
BEGIN
SELECT @id = 'Cp-' + CAST(MAX(CAST(SUBSTRING(CompanyCode,4,5) AS
INTEGER))+1 AS VARCHAR) FROM [Beauty Saloon
Project].[dbo].[tbl_Company];
IF @id IS NULL
BEGIN
SET @id = 'Cp-00001';
END
RETURN @id;
END
but when i call it here
datatable DT = new datatable
DT = ExecuteSpDataTable("sp_AutoGenerateCustomerCode");
This returns null.
If I don't have data then it should return Cp-00001, but I have one data
row in which company code is saloon is it the reason for null ???