Auto Generate New Id in MS SQL in Procedure

USE [CIF_DB]
GO
/****** Object:  StoredProcedure [dbo].[GetRequestNo]    Script Date: 1/8/2020 6:11:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--EXEC GetRequestNo
ALTER PROCEDURE [dbo].[GetNewCIF_ID]
AS
BEGIN

DECLARE @RequestNo varchar(20),@maxCount bigint

select @maxCount =  count(*) from [dbo].[CIF_Requests]

if(@maxCount <> 0)
SET @RequestNo =  'TIF_'+RIGHT('000000' + CONVERT(nvarchar(20), @maxCount + 1), 6)
    else
 
    SET @RequestNo =  'TIF_'+RIGHT('000000' + CONVERT(nvarchar(20), 1), 6)


SELECT @RequestNo
 END