主要有三个字段:Model,Item和MaxValue,
建立存储过程GetMakeManIDValue如下:
CREATE
Proc
dbo.GetMakeManIDValue
(
@MaxValue int output,
@Model varchar ( 10 ),
@Item varchar ( 30 )
) AS
declare @errorNb int
Begin Tran
select @MaxValue = MaxValue from MakeManID with (UPDLOCK) Where Model = @Model and Item = @Item
if ( @MaxValue is null )
begin
insert into MakeManID(Model,Item) values ( @Model , @Item )
set @errorNb = @@error
if ( @errorNb != 0 ) goto errHandle
set @MaxValue = 1
end else
begin
set @MaxValue = @MaxValue + 1
update MakeManID set MaxValue = MaxValue + 1 where Model = @Model and Item = @Item
set @errorNb = @@error
if ( @errorNb != 0 ) goto errHandle
end
commit Tran
return
errHandle:
rollback tran
RAISERROR ( @errorNb , 16 , 1 )
set @MaxValue = 0
return
GO
存储过程主要完成得到MaxValue的值,然后加1.
(
@MaxValue int output,
@Model varchar ( 10 ),
@Item varchar ( 30 )
) AS
declare @errorNb int
Begin Tran
select @MaxValue = MaxValue from MakeManID with (UPDLOCK) Where Model = @Model and Item = @Item
if ( @MaxValue is null )
begin
insert into MakeManID(Model,Item) values ( @Model , @Item )
set @errorNb = @@error
if ( @errorNb != 0 ) goto errHandle
set @MaxValue = 1
end else
begin
set @MaxValue = @MaxValue + 1
update MakeManID set MaxValue = MaxValue + 1 where Model = @Model and Item = @Item
set @errorNb = @@error
if ( @errorNb != 0 ) goto errHandle
end
commit Tran
return
errHandle:
rollback tran
RAISERROR ( @errorNb , 16 , 1 )
set @MaxValue = 0
return
GO
2.建立一个man表,里面有组织ID,职工姓名等等字段。
然后添加一个存储过程Add_Man.
CREATE
PROCEDURE
Add_Man
@GroupID int ,
@Name varchar ( 20 ),
@School varchar ( 20 ),
@Qualifications varchar ( 20 ),
@Positions int ,
@Salary int ,
@Evaluation varchar ( 255 )
AS
set xact_abort on
declare @ManID char ( 10 )
declare @D datetime
set @D = GetDate ()
declare @strY char ( 4 )
Declare @Item varchar ( 7 )
set @Item = right ( ' 000 ' + convert ( varchar ( 3 ), @GroupID ), 3 ) + convert ( varchar ( 4 ), DatePart (yyyy, @D ))
declare @max int
exec dbo.GetMakeManIDValue @max out , ' ManID ' , @Item
set @ManID = @Item +right ( ' 000 ' + convert ( varchar ( 3 ), @max ), 3 )
begin tran
insert into Man( [ ManID ] , [ PassWord ] , [ Name ] , [ School ] , [ Qualifications ] , [ Positions ] , [ Salary ] , [ Evaluation ] )
values ( @ManID , ' 123456 ' , @Name , @School , @Qualifications , @Positions , @Salary , @Evaluation )
if @@Error != 0 goto errHandle
commit tran
return
errHandle:
set @ManID = ''
rollback tran
return
GO
存储过程主要完成能够根据存储过程GetMakeManIDValue来设置ManID和添加用户信息!!
@GroupID int ,
@Name varchar ( 20 ),
@School varchar ( 20 ),
@Qualifications varchar ( 20 ),
@Positions int ,
@Salary int ,
@Evaluation varchar ( 255 )
AS
set xact_abort on
declare @ManID char ( 10 )
declare @D datetime
set @D = GetDate ()
declare @strY char ( 4 )
Declare @Item varchar ( 7 )
set @Item = right ( ' 000 ' + convert ( varchar ( 3 ), @GroupID ), 3 ) + convert ( varchar ( 4 ), DatePart (yyyy, @D ))
declare @max int
exec dbo.GetMakeManIDValue @max out , ' ManID ' , @Item
set @ManID = @Item +right ( ' 000 ' + convert ( varchar ( 3 ), @max ), 3 )
begin tran
insert into Man( [ ManID ] , [ PassWord ] , [ Name ] , [ School ] , [ Qualifications ] , [ Positions ] , [ Salary ] , [ Evaluation ] )
values ( @ManID , ' 123456 ' , @Name , @School , @Qualifications , @Positions , @Salary , @Evaluation )
if @@Error != 0 goto errHandle
commit tran
return
errHandle:
set @ManID = ''
rollback tran
return
GO