用例级别存储过程
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 --自动化用例数
public static String rea