Try this one -
CREATE PROC [dbo].[getVoucherNo]
AS BEGIN
DECLARE
@Prefix VARCHAR(10) = 'J'
, @startFrom INT = 1
, @maxCode VARCHAR(100)
, @sCode INT
IF EXISTS(
SELECT 1
FROM dbo.Journal_Entry
) BEGIN
SELECT @maxCode = CAST(MAX(CAST(SUBSTRING(Voucher_No,LEN(@startFrom)+1,ABS(LEN(Voucher_No)- LEN(@Prefix))) AS INT)) AS varchar(100))
FROM dbo.Journal_Entry;
SELECT @Prefix +
CAST(LEN(LEFT(@maxCode, 10) + 1) AS VARCHAR(10)) + -- !!! possible problem here
CAST(@maxCode AS VARCHAR(100))
END
ELSE BEGIN
SELECT (@Prefix + CAST(@startFrom AS VARCHAR))
END
END