SQL Server stored procedure generates a letter + number encoding

The equipment establish accounts function in the company's equipment management system, the function is to record the new equipment and put it into storage. One of the device number is defined as self-modification (see the previous device number, take a non-duplicate value to fill out), feel particularly troublesome! It is not more efficient to generate code automatically with stored procedures.

Requirements: According to the device Group code (PC) + increment number, constitute the encoding, the number must be three digits, no three-digit, with ' 0 ' placeholder.

The code is as follows:　

1 --Test Database Preparation2  UseMaster3 Go4 if exists(Select *  fromDbo.sysdatabaseswhereName='Hzf')5 Drop DatabaseHzf6 Go7 Create DatabaseHzf8 Go9  UseHzfTen Go One --Create a test table A ALTER proc [dbo].[get_new_id]  -     @new_id varchar( -) Output,--Number of production -     @number int,--set the maximum number of digits for an encoded number the     @group varchar(5)--numbered Letters -  as - begin -   +     Set @new_id = NULL -       +     Select Top 1 @new_id =Id fromtest_idwhereId like @group+'%' Order  by Convert(int, Right(ID,LEN(ID)-Len(@group)) )desc A       at     if @new_id  is NULL -         Set @new_id = @group+REPLICATE('0',2)+'1' -     Else -         begin -             Declare @big varchar( -) -             Set @big = Convert(int, Right(@new_id,LEN(@new_id)-Len(@group)) )+1  in             if @big < - -             begin to                 Set @big = REPLICATE('0',3-LEN(@big))+ @big +             End -             Set @new_id = @group + @big  the         End *       $  Panax Notoginseng End -   the GO +   A --Test the Declare @test int + Set @test=1 -  while(@test<= -) $ begin $     Declare @new_id    varchar( -) -     execget_new_id@new_idOutput7,'PC' -     Insert  intotest_idValues(@new_id) the     Set @test = @test+1 - EndWuyi Select *  fromtest_id