sql脚本

use tempdb;
go
if exists(select 1 from sysobjects where id=object_id('日志表') and type='U')
    drop table 日志表
create table 日志表 (
   顺序号                  numeric(20)          identity ,
   操作类型                 nvarchar(20)         null,
   操作对象                 varchar(30)          null,
   操作用户                 varchar(30)        null default suser_sname(),
   操作日期                 smalldatetime      null default getdate(),
   constraint PK_日志表 primary key  (顺序号)
)

go

if exists (select 1
            from  sysobjects
           where  id = object_id('tCLASS')
            and   type = 'U')
   drop table tCLASS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tSTUDENT')
            and   type = 'U')
   drop table tSTUDENT
go

/*==============================================================*/
/* Table: tCLASS                                                */
/*==============================================================*/
create table tCLASS (
   CLASSID              numeric              not null,
   CLASSNAME            varchar(20)          null,
   constraint PK_TCLASS primary key  (CLASSID)
)
go

/*==============================================================*/
/* Table: tSTUDENT                                              */
/*==============================================================*/
create table tSTUDENT (
   STUDENTID            varchar(15)          not null,
   CLASSID              numeric              null,
   STUDENTNAME          varchar(20)          null,
   Gentle               bit                  null,
   Picture              image                null,
   constraint PK_TSTUDENT primary key  (STUDENTID)
)
go

alter table tSTUDENT
   add constraint FK_TSTUDENT_REFERENCE_TCLASS foreign key (CLASSID)
      references tCLASS (CLASSID)
go


insert into tCLASS values(1,'06可视化1班')
insert into tCLASS values(2,'06可视化2班')
insert into tCLASS values(3,'06数据库')
insert into tCLASS values(4,'06WEB1班')
insert into tCLASS values(5,'06WEB2班')
insert into tCLASS values(6,'06移动班')
go

create procedure pUptStudent
  @StudentID varchar(30),
  @classid integer,
  @studentname varchar(30),
  @errstr varchar(80) output
 as
begin
   if @classid is null
    begin
       select @errstr='班级编号不能为空!'
       return -1000
   end
  
   if not exists (select classname from tCLASS where CLASSID=@classid)
    begin
      select @errstr='未找到对应的班级!'
      return -1001   /*未找到对应的班级*/
    end
   
    begin tran
      update tSTUDENT set CLASSID=@classid,STUDENTNAME=@Studentname
             where   STUDENTID=@StudentID
      if @@rowcount=0
       begin
   rollback tran
           select @errstr='未找到相应的学号'
           return -1002
      end

      if @@error  <>0
       begin
  rollback tran
          select @errstr='修改失败!'
          return -1003
      end

     insert into  日志表(操作类型,操作对象)  
         values('修改','tSTUDENT.'+@StudentID)

     if @@error <>0
       begin
         rollback tran
         select @errstr='修改失败,插入日志不成功!'
         return -1004
     end
     commit tran
     return 0
end

go