Auto generate Sequence Number in Sql server

create function [Function_Name]
(
@Parameter        TINYINT
,@Parameter2        TINYINT

)
RETURNS NVARCHAR(100)
AS
BEGIN

    DECLARE      @Name    NVARCHAR(100)
            , @Code            NVARCHAR(25)
            , @AutoGenerateNumber VARCHAR(100)


    SELECT @Name    = 'CompanyName'   
          ,@Code = 'Comapny Code'
    FROM dbo.[TableName] FBS
    INNER JOIN [dbo].[TableName2] FB
        ON FBS.ColumnName=FB.ColumnName
    INNER JOIN [dbo].[TableName3] SM
        ON SM.ColumnName=FBS.ColumnName
    WHERE FBS.ColumnName = @Parameter
        AND FBS.ColumnName = @Parameter2
   
    SELECT @AutoGenerateNumber=@Name+ '/'+@Code +'/'+right('0000' + CONVERT(VARCHAR(50),MAX(REVERSE(SUBSTRING(REVERSE(ReferenceNo),0,CHARINDEX('/',REVERSE(ISNULL(ReferenceNo,0)))))+1)),4)  
    FROM dbo.TableName A
    WHERE A.FundBlockId = @Parameter
        AND A.SchemeId =@Parameter2

    IF @AutoGenerateNumber IS NULL
        SET @AutoGenerateNumber=@Name+ '/'+@Code +'/0001'

        RETURN @AutoGenerateNumber
   
END