Auto Generate Transid function [Com. personal]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[GenerateTRSerial]
( 
@param1 varchar(3), 
@ParaDate datetime)RETURNS varchar(8) 
AS 
BEGIN 
declare @AutoGenNo varchar(8)
Select @AutoGenNo=@param1 + 
right(convert(varchar,Year(@ParaDate)),2)+
right('000'+convert(varchar,isnull(convert(int,right(Max([TransIdfromexistingSerialNo]),3)), 35)+1),3)
FROM [CBU].[dbo].[Table]
where (year([Date])=year(@ParaDate) or year([stamp])=year(@ParaDate)) and [Id]=@param1
return @AutoGenNo
END