SQL 语句创建数据库、表与存储过程的样例

 

 

 

--1、创建数据库CaseManage

 

If exists(select  name from master.dbo.sysdatabases where name = N'CaseManage')

 

  drop database CaseManage

 

Go

 

create database CaseManage

 

on

 

(

 

   name = 'CaseManage_Data',

 

   fileName = 'D:\Program Files\Borland\Delphi7\Projects\DB\CaseManage_Data.MDF',

 

   size = 10MB,

 

   maxsize = 50MB,

 

   filegrowth = 5MB

 

)

 

log on

 

(

 

   name = 'CaseManage_Log',

 

   fileName = 'D:\Program Files\Borland\Delphi7\Projects\DB\CaseManage_Data.LDF',

 

   size = 5MB,

 

   maxsize = 25MB,

 

   filegrowth = 5MB

 

)

 

Go

 

 

 

--2、创建项目信息表BL_TBL_Project

 

use CaseManage

 

if exists(select * from sysobjects where id = object_id('BL_TBL_Project'))

 

  drop table BL_TBL_Project

 

go

 

create table BL_TBL_Project

 

(

 

   ProjectID int not null,

 

   ProjectName nvarchar(100) not null,

 

   ProjectSpell nvarchar(20),

 

   Responser nvarchar(20),

 

   Remark nvarchar(200),

 

   ProjectStatus bit,

 

   constraint PK_BL_TBL_Project primary key(ProjectID)

 

)

 

Go

 

 

 

 

 

--3、创建用例信息表BL_TBL_Case

 

use CaseManage

 

if exists(select * from sysobjects where id = object_id('BL_TBL_Case'))

 

  drop table BL_TBL_Case

 

go

 

create table BL_TBL_Case

 

(

 

   CaseID int not null,

 

   FatherNode int ,

 

   CaseName nvarchar(50) not null,

 

   CaseSpell  nvarchar(20),

 

   RelService nvarchar(100),

 

   ProcName   nvarchar(20)

 

   DataName   nvarchar(20)  l,

 

   ParamName  nvarchar(20)  ,

 

   Remark     nvarchar(200),

 

   ProjectID  int not null,

 

   constraint PK_BL_TBL_Case primary key(CaseID)

 

)

 

Go

 

 

--4、存储过程（编辑测试用例的信息）

 

CREATE PROCEDURE BL_HV_EditCaseInfo

(

   @EditType varchar(10),   --'Insert' :表示添加用例; ‘Update’:表示修改用例

    @CaseID  int,

   @FatherNode  int,

   @CaseName nvarchar(50),

   @CaseSpell  nvarchar(20),

   @RelService nvarchar(100),

   @ProcName   nvarchar(20),

   @DataName   nvarchar(20),

   @ParamName  nvarchar(20),

   @Remark     nvarchar(200),

   @ProjectID  int,

 

   @ErrorCode INT OUTPUT,

   @ErrorMessage VARCHAR(100) OUTPUT

)

as

begin

   set @ErrorCode = 0;

   set @ErrorMessage = '执行<BL_HV_EditCaseInfo>(编辑用例信息)成功。';

 

   ------------------------添加用例信息---------------------------------------------------------------------------  

   if @EditType = 'Insert'

   begin

     ------------------- 检验用例ID是否可用---------------------------------------------

     if  exists(select 1 from BL_TBL_Case where CaseID = @CaseID)

     begin

        set @ErrorCode = 1;

        set @ErrorMessage ='对不起！用例ID重复。';

        return;

     end

 

     -------------------添加用例信息的SQL语句-------------------------------------------

     insert into BL_TBL_Case(CaseID,FatherNode ,CaseName,

                             CaseSpell ,RelService,ProcName,DataName,ParamName,Remark,ProjectID)

                      values(@CaseID,@FatherNode ,@CaseName,

                             @CaseSpell ,@RelService,@ProcName,@DataName,@ParamName,@Remark,@ProjectID);

   end

   =============================================================================================================

 

   ----------------修改用例信息-----------------------------------------------------------------------------------

   if @EditType = 'Update'

   begin

     ----------------检验用例ID是否存在-------------------------------------------------

     if not exists(select 1 from BL_TBL_Case where CaseID = @CaseID)

     begin

       set @ErrorCode = 3;

       set @ErrorMessage = '对不起！用例ID不存在。';

       return;

     end

     

     ----------------修改用例信息的SQL语句----------------------------------------------

     update  BL_TBL_Case

        set  FatherNode = @FatherNode,

             CaseName = @CaseName,

             CaseSpell = @CaseSpell,

             RelService = @RelService,

             ProcName = @ProcName,

             DataName = @DataName,

             ParamName = @ParamName,

             Remark = @Remark,

             ProjectID = @ProjectID

      where  CaseID = @CaseID

    end

    =============================================================================================================

 

    ------------------------------判断是否错误，错误就滚回，没有错误就执行---------------------------------------------

    if @@Error<>0

    begin

      set @ErrorCode = @@Error;

    end

  

    if @ErrorCode<> 0

      set @ErrorMessage = '执行<BL_HV_EditCaseInfo>(编辑用例信息)失败。';

end

GO