A couple of issues here:
1. Concurrency - wrapping your code in a transaction is not going to ensure that it cannot be executed concurrently by two or more processes, resulting in potential duplicates being generated. The line
????????????????
SET @CurrentSID = (SELECT [SID] FROM [RefVal_SerialID])
could very easily be executed by two processes and get the same result.
There was a post on this very topic somewhere here recently when I find it I'll post the link.
2. Left padding with zeroes - don't need all those IF's, just do it like this:
SELECT RIGHT('0000000000' + cast(someInt as varchar(50)),10)