用存储过程获得表的自定义ID

在许多情况下对一个表里主键想采用我们定义的ID，今天把获得表ID写了个存储过程以后用到直接复制了！给大家贴出来参考下了！

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Student]

GO

 

CREATE TABLE [dbo].[Student] (

       [StudentID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

       [StudentName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

       [StudentSex] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

Create  Proc GetTableId

@IdStr     VarChar(10),

@IdNumLen  Int

As

  Declare @MaxId VarChar(50),

          @MaxNum int

  Select @MaxId=Max(StudentID) From Student  where StudentID Like @IdStr +'%'

  If @MaxId Is null

     Set @MaxNum='0';

  Else

     Set @MaxNum=Cast(replace(@MaxId,@IdStr,'') as int)

  Set @MaxNum=@MaxNum+1

  Set @MaxId=Cast(@MaxNum as VarChar(10))

  While(Len(@MaxId)<@IdNumLen)

      Set @MaxId='0'+@MaxId

  Set @MaxId=@IdStr+@MaxId

Print @MaxId