sql server - Auto generate by default, first two digits are the last two digits of current year -

i want work ms sql functions. project number should automatically generated default, first 2 digits last 2 digits of current year, ‘-’ last 3 digits serial number based on year.

e.g 16-000 next 16-001 on when 2017 comes change again 17-000 , continue.

here achievement , need kind of modification throws exception "conversion failed when converting varchar value '16-001' data type int." when portion being execute. set @pid =(select right(convert(varchar(8), year(getdate()), 1),2)) +'-'+ right(@pid,3)+1

my function complete code.

create function nextprojectnumber() returns char(6) begin declare @pid varchar(50), @pyear int, @serial varchar(3), @curryear int
select @pid = projectid [jobportaldb].[dbo].[projects] projectid=(select max(projectid) [jobportaldb].[dbo].[projects]) set @curryear = (select right(convert(varchar(8), year(getdate()), 1),2)) --select @pid,@year

if @pid null set @pid = (select right(convert(varchar(8), year(getdate()), 1),2))+'-001' else set @pyear = substring(@pid, 1, 2)

if @pyear = @curryear set @pid =(select right(convert(varchar(8), year(getdate()), 1),2)) +'-'+ right(@pid,3)+1

if @curryear > @pyear set @pid = (select right(convert(varchar(8), year(getdate()), 1),2)) +'-001'

return @pid end