用例级别存储过程

Sql代码

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================TESTCASE

-- Description: 测试视图->用例级别:统计信息

-- @version 版本

-- @type 类型

-- @likeName 模糊查询的关键词

-- @tdtName tdt名称

-- @showNum 每页显示数

-- @start 从哪开始

--类型不为TDT的用例级别统计信息

-- =============================================

ALTER PROCEDURE [dbo].[sp_iKnowDashboard_TestCase_Level]

(

@version varchar(255),

@type varchar(255),

@likeName varchar(255),

@tdtName varchar(255),

@showNum varchar(25),

@start varchar(25)

)

as

SET NOCOUNT ON

declare @RunSQL nvarchar(MAX)

declare @testcaseTable varchar(255)

declare @statisticsTable varchar(255)

declare @storyTable varchar(255)

declare @typeCode varchar(255) -- 类型code

declare @typeName varchar(255) -- 用例名字

set @testcaseTable = 'IKNOW_TESTCASE_'+@version

set @statisticsTable = 'IKNOW_STATISTICS_'+@version

set @storyTable = 'IKNOW_STORY_'+@version

set @typeName = @type + 'NAME' -- 用例名对应的字段

set @typeCode = @type + 'CODE' -- 取得类型code值

SET @RunSQL='

declare @temporary_Statistics_tmpTable TABLE(

NAME varchar(255),

TYPE varchar(255),

EXAMPLE_GUID varchar(255),

STORY_URL varchar(255),

EXAMPLE_TOTAL float,

ZERO_LEVEL_SCALE float,

ONE_LEVEL_SCALE float,

TWO_LEVEL_SCALE float,

THREE_LEVEL_SCALE float,

FOUR_LEVEL_SCALE float,

FIVE_LEVEL_SCALE float,

SIX_LEVEL_SCALE float,

AUTOMATIC_EXAMPLE_SCALE float,

COUNTNUM float

);

declare @typeCodeTable table( --申明临时表@typeCodeTable，用例表的值插入临时表disinct

guid varchar(255),

storyLink varchar(255),

rmids varchar(255),

name varchar(255),

rowNum int );

if object_id(''tempdb..#UserDataTable'') is not null

begin

drop table #UserDataTable

end

select guid,linkid,rmid,name,row_number() over(order by name desc) rowNum into #UserDataTable from '+@statisticsTable+'

where type = '''+@type+''' and name like ''%'+@likeName+'%'' and tdtname like ''%'+@tdtName+'%''

insert into @typeCodeTable(guid,storyLink,rmids,name,rowNum)

select guid,linkid,rmid,name,row_number() over(order by rmid) from #UserDataTable

where rowNum > '+@start+' and rowNum <= ('+@start+' + '+@showNum+');

declare @count int,

@typeCodeID varchar(255),

@NAME varchar(255),

@TYPE varchar(255),

@EXAMPLE_GUID varchar(255),

@EXAMPLE_RMID varchar(255),

@STORY_URL varchar(255),

@EXAMPLE_TOTAL float,--用例总数

@ZERO_LEVEL_SCALE float,

@ONE_LEVEL_SCALE float,

@TWO_LEVEL_SCALE float,

@THREE_LEVEL_SCALE float,

@FOUR_LEVEL_SCALE float,

@FIVE_LEVEL_SCALE float,

@SIX_LEVEL_SCALE float,

@AUTOMATIC_EXAMPLE_SCALE float,

@COUNTNUM float,

@AUTOMATIC float --自动化用例数