Please, I'm a beginner.

After reading Larry Sumuri tutorial on Seral ID Auto generation, I created a procedure.

create table RefVal_SID

(

SID int not null,

constraint pk_refval_sid primary key clustered (SID asc)

)

create procedure AutogenerateID

@startchar char(1), --first letter

@result varchar(8) output

as

begin

set nocount on;

declare @CurrentSID int

declare @CurrentYear Varchar(4)

declare @UniqueID varchar(8)

declare @Lenght int

declare @GeneratedID int

declare @ConvID varchar(3)

begin try

begin TRAN mytran

set @CurrentSID=(select SID from RefVal_SID)

set @CurrentYear=(select year(getdate()))

if @CurrentSID is null begin

set @CurrentSID=1

end

else

begin

set @CurrentSID=@CurrentSID+1

end

set @GeneratedID=@CurrentSID

set @ConvID=Convert(Varchar(3),@GeneratedID)

set @Lenght=Len(@GeneratedID)

if @Lenght = 1 begin

set @UniqueID = @startchar+@CurrentYear+'00'+@ConvID

end

if @Lenght = 2

begin

set @UniqueID = @startchar+@CurrentYear+'0'+@ConvID

end

if @Lenght=3

set @UniqueID =@startchar+@CurrentYear+@ConvID

insert into RefVal_SID

values(@CurrentSID)

delete from RefVal_SID where SID <>@CurrentSID

COMMIT TRAN mytran

set @Result = @UniqueID

end try

begin catch

rollback tran mytran

set @Result = 'Failed'

end catch

end

I created this table to test the procedure but I don't really know how to execute the procedure inside.

create table test

(

testID varchar(8) as execute AutogenerateID 'T',

myname varchar(25),

constraint pk_test primary key clustered (testID asc)

)

Please help me