stored procedure concatenation problem

[beaniebear]

When I add a new member of staff to a table I require a reference to be added to the reference field which is year/nextrefence ie 2009/0001. Only the year is being displayed. Could you please let me know where I am going wrong? Or if there is a better way to do what I' trying to do?

Insert Into Staff(LastName) values ('smith')

declare @ID int
declare @Counter int
declare @NextRef char(9)
declare @Year int

set @ID = scope_identity()

update NextReference set NextReference.Counter=NextReference.Counter +1 where NextReferenceID=1
select @Counter=counter from nextreference where NextReferenceID=1
set @Year=year(getdate())

set @NextRef = cast(@year as char) + '/' + cast(@Counter as char)

Update Staff set Reference = @NextRef where BookingID=@ID

 

[beaniebear]

select @NextRef =(cast(@year as char) + '/' + cast(@Counter as char))

changed this to

select @NextRef =(cast(@year as char(4)) + '/' + cast(@Counter as char(4)))

now it works, phew!

Still be interested to hear if there is a better solution to this? Thanks.