这是一张存储过程参数表
ID对应的是存储过程的ID，（外键对应的是存储过程主信息表的ID）
index 对应的是参数的序号,
param 参数的名字
badvalue 错误的参数值
badtype 错误值的类型
badreturnvalue 错误的返回值ID index param badvalue badtype badreturnvalue
1 1 @Accounts 45645 1 1
1 2 @Pwd 456 2 1
1 3 @LoginIP 1131 1 1
1 4 @OutPutValue 8978 3 2
2 1 @Accounts 4656 4 4
3 1 @UserId 45645 5 2
11 1 @UserID 456456 4 4
12 1 @ServerID 45645 8 8
13 1 @ServerID 456456 9 7另外一张存储过程主信息表ID：存储过程的ID，
name: 存储过程的名字，
backtype:返回值类型
rightvalue：返回值预期的正确值，
ID name backtype rightvalue
1 UP_UserInfo_Login 1 0
2 UP_UI_ID_OneUserInfo_S 2 1
3 UP_MIf_DoorTicket_S 2 2
4 UP_TGt_HavePrizeLayout_S 2 9
5 UP_TGt_ZorePrizeLayout_S 2 4
6 UP_SSt_ServerList_S 2 1
7 UP_TGt_ZorePrizeRunTerm_S 2 4
8 UP_TGt_HavePrizeRunTerm_S 2 9
9 UP_TGt_HallSeverList_S 2 1
10 UP_DDo_List_S 2 16
11 UP_UIf_UID_Coin_S 2 1
12 UP_SSt_ID_S 2 1
13 UP_SSt_ID_Port_S 2 1第三张存储过程执行表:
表示存储过程每次的执行情况 ,ID 和 index列可以是相同的。id 对应的存储过程，
index 对应的存储过程参数的序号
RndInput 输入的测试的值
BadBack 返回值
backtime 返回时间
NeedTime 需要时间
ID Index RndInput BadBack backtime NeedTime
1 1 465654 1 2008-05-20 00:00:00 300
1 2 456645 1 2008-05-20 00:00:00 200
1 3 4554455 1 2008-05-20 00:00:00 10
1 4 4645 1 2008-05-20 00:00:00 200
1 1 00001 1 2008-05-20 00:00:00 300
1 2 00022 1 2008-05-20 00:00:00 200
1 3 00003 1 2008-05-20 00:00:00 10
1 4 00004 1 2008-05-20 00:00:00 200转换成:
param2 param3,param4 ...paramN 与param1相同 有对应错误的参数值，对应的返回值类型，对应返回值预期的正确值....表示省略
ID rightvalue backTime param1 badvalue1 badtype1 badreturnvalue1 RndInput1 badback1 param2 .... param3 .... param4 ....
1 0 2008-05-20 @Accounts 45645 1 1 465654 1 @Pwd @LoginIP @OutPutValue
1 0 2008-05-20 @Accounts 45645 1 1 00022 1
2 1 2008-05-20 @Accounts .... .... ....
3 2 ... @UserId .... .... ....
11 1 ... @UserID .... .... ....
12 1 ... @ServerID .... .... ....
13 1 ... @ServerID .... .... ....
1 0 2008-05-20 @Accounts 45645 1 1 00022 1存储过程ID和它的所有参数 信息三张表都可以通过ID关联，第二张表与第三张表的index是存储过程的序号是关联的。
ID对应的是存储过程的ID，（外键对应的是存储过程主信息表的ID）
index 对应的是参数的序号,
param 参数的名字
badvalue 错误的参数值
badtype 错误值的类型
badreturnvalue 错误的返回值ID index param badvalue badtype badreturnvalue
1 1 @Accounts 45645 1 1
1 2 @Pwd 456 2 1
1 3 @LoginIP 1131 1 1
1 4 @OutPutValue 8978 3 2
2 1 @Accounts 4656 4 4
3 1 @UserId 45645 5 2
11 1 @UserID 456456 4 4
12 1 @ServerID 45645 8 8
13 1 @ServerID 456456 9 7另外一张存储过程主信息表ID：存储过程的ID，
name: 存储过程的名字，
backtype:返回值类型
rightvalue：返回值预期的正确值，
ID name backtype rightvalue
1 UP_UserInfo_Login 1 0
2 UP_UI_ID_OneUserInfo_S 2 1
3 UP_MIf_DoorTicket_S 2 2
4 UP_TGt_HavePrizeLayout_S 2 9
5 UP_TGt_ZorePrizeLayout_S 2 4
6 UP_SSt_ServerList_S 2 1
7 UP_TGt_ZorePrizeRunTerm_S 2 4
8 UP_TGt_HavePrizeRunTerm_S 2 9
9 UP_TGt_HallSeverList_S 2 1
10 UP_DDo_List_S 2 16
11 UP_UIf_UID_Coin_S 2 1
12 UP_SSt_ID_S 2 1
13 UP_SSt_ID_Port_S 2 1第三张存储过程执行表:
表示存储过程每次的执行情况 ,ID 和 index列可以是相同的。id 对应的存储过程，
index 对应的存储过程参数的序号
RndInput 输入的测试的值
BadBack 返回值
backtime 返回时间
NeedTime 需要时间
ID Index RndInput BadBack backtime NeedTime
1 1 465654 1 2008-05-20 00:00:00 300
1 2 456645 1 2008-05-20 00:00:00 200
1 3 4554455 1 2008-05-20 00:00:00 10
1 4 4645 1 2008-05-20 00:00:00 200
1 1 00001 1 2008-05-20 00:00:00 300
1 2 00022 1 2008-05-20 00:00:00 200
1 3 00003 1 2008-05-20 00:00:00 10
1 4 00004 1 2008-05-20 00:00:00 200转换成:
param2 param3,param4 ...paramN 与param1相同 有对应错误的参数值，对应的返回值类型，对应返回值预期的正确值....表示省略
ID rightvalue backTime param1 badvalue1 badtype1 badreturnvalue1 RndInput1 badback1 param2 .... param3 .... param4 ....
1 0 2008-05-20 @Accounts 45645 1 1 465654 1 @Pwd @LoginIP @OutPutValue
1 0 2008-05-20 @Accounts 45645 1 1 00022 1
2 1 2008-05-20 @Accounts .... .... ....
3 2 ... @UserId .... .... ....
11 1 ... @UserID .... .... ....
12 1 ... @ServerID .... .... ....
13 1 ... @ServerID .... .... ....
1 0 2008-05-20 @Accounts 45645 1 1 00022 1存储过程ID和它的所有参数 信息三张表都可以通过ID关联，第二张表与第三张表的index是存储过程的序号是关联的。
ID对应的是存储过程的ID，（外键对应的是存储过程主信息表的ID）
index 对应的是参数的序号,
param 参数的名字
badvalue 错误的参数值
badtype 错误值的类型
badreturnvalue 错误的返回值ID index param badvalue badtype badreturnvalue
1 1 @Accounts 45645 1 1
1 2 @Pwd 456 2 1
1 3 @LoginIP 1131 1 1
1 4 @OutPutValue 8978 3 2
2 1 @Accounts 4656 4 4
3 1 @UserId 45645 5 2
11 1 @UserID 456456 4 4
12 1 @ServerID 45645 8 8
13 1 @ServerID 456456 9 7另外一张存储过程主信息表ID：存储过程的ID，
name: 存储过程的名字，
backtype:返回值类型
rightvalue：返回值预期的正确值，
ID name backtype rightvalue
1 UP_UserInfo_Login 1 0
2 UP_UI_ID_OneUserInfo_S 2 1
3 UP_MIf_DoorTicket_S 2 2
4 UP_TGt_HavePrizeLayout_S 2 9
5 UP_TGt_ZorePrizeLayout_S 2 4
6 UP_SSt_ServerList_S 2 1
7 UP_TGt_ZorePrizeRunTerm_S 2 4
8 UP_TGt_HavePrizeRunTerm_S 2 9
9 UP_TGt_HallSeverList_S 2 1
10 UP_DDo_List_S 2 16
11 UP_UIf_UID_Coin_S 2 1
12 UP_SSt_ID_S 2 1
13 UP_SSt_ID_Port_S 2 1第三张存储过程执行表:
表示存储过程每次的执行情况 ,ID 和 index列可以是相同的。id 对应的存储过程，
index 对应的存储过程参数的序号
RndInput 输入的测试的值
BadBack 返回值
backtime 返回时间
NeedTime 需要时间
ID Index RndInput BadBack backtime NeedTime
1 1 465654 1 2008-05-20 00:00:00 300
1 2 456645 1 2008-05-20 00:00:00 200
1 3 4554455 1 2008-05-20 00:00:00 10
1 4 4645 1 2008-05-20 00:00:00 200
1 1 00001 1 2008-05-20 00:00:00 300
1 2 00022 1 2008-05-20 00:00:00 200
1 3 00003 1 2008-05-20 00:00:00 10
1 4 00004 1 2008-05-20 00:00:00 200
转换成:
param2 param3,param4 ...paramN 与param1相同 有对应错误的参数值，对应的返回值类型，对应返回值预期的正确值....表示省略
ID rightvalue backTime param1 badvalue1 badtype1 badreturnvalue1 RndInput1 badback1 param2 .... param3 .... param4 ....
1 0 2008-05-20 @Accounts 45645 1 1 465654 1 @Pwd @LoginIP @OutPutValue
1 0 2008-05-20 @Accounts 45645 1 1 00022 1
2 1 2008-05-20 @Accounts .... .... ....
3 2 ... @UserId .... .... ....
11 1 ... @UserID .... .... ....
12 1 ... @ServerID .... .... ....
13 1 ... @ServerID .... .... ....
1 0 2008-05-20 @Accounts 45645 1 1 00022 1存储过程ID和它的所有参数 信息三张表都可以通过ID关联，第二张表与第三张表的index是存储过程的序号是关联的。
现在有三张表了，
而且每一行会相应的动态的添加加一些列
如:
RndInput1 badback1
转成
这种形式的ID rightvalue backTime param1 badvalue1 badtype1 badreturnvalue1 RndInput1 badback1 param2 .... param3 .... param4 ....
1 0 2008-05-20 @Accounts 45645 1 1 465654 1 @Pwd @LoginIP @OutPutValue
1 0 2008-05-20 @Accounts 45645 1 1 00022 1
2 1 2008-05-20 @Accounts .... .... ....
3 2 ... @UserId .... .... ....
11 1 ... @UserID .... .... ....
12 1 ... @ServerID .... .... ....
13 1 ... @ServerID .... .... ....
1 0 2008-05-20 @Accounts 45645 1 1 00022 1
backTime RndInput1 badback1 等类似的这些列来自第三张表
param1 badvalue1 badtype1 badreturnvalue1 等类似的这些列来自第二张表
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(ID int,[index] int,param varchar(20),badvalue int,badtype int,badreturnvalue int)
Go
Insert into Ta
select 1,1,'@Accounts',45645,1,1 union all
select 1,2,'@Pwd',456,2,1 union all
select 1,3,'@LoginIP',1131,1,1 union all
select 1,4,'@OutPutValue',8978,3,2 union all
select 2,1,'@Accounts',4656,4,4 union all
select 3,1,'@UserId',45645,5,2 union all
select 11,1,'@UserID',456456,4,4 union all
select 12,1,'@ServerID',45645,8,8 union all
select 13,1,'@ServerID',456456,9,7
Go
-- Test Data: poc
If object_id('poc') is not null
Drop table poc
Go
Create table poc(ID int,name varchar(57),backtype int,rightvalue int)
Go
Insert into poc
select 1,'UP_UserInfo_Login',1,0 union all
select 2,'UP_UI_ID_OneUserInfo_S',2,1 union all
select 3,'UP_MIf_DoorTicket_S',2,2 union all
select 4,'UP_TGt_HavePrizeLayout_S',2,9 union all
select 5,'UP_TGt_ZorePrizeLayout_S',2,4 union all
select 6,'UP_SSt_ServerList_S',2,1 union all
select 7,'UP_TGt_ZorePrizeRunTerm_S',2,4 union all
select 8,'UP_TGt_HavePrizeRunTerm_S',2,9 union all
select 9,'UP_TGt_HallSeverList_S',2,1 union all
select 10,'UP_DDo_List_S',2,16 union all
select 11,'UP_UIf_UID_Coin_S',2,1 union all
select 12,'UP_SSt_ID_S',2,1 union all
select 13,'UP_SSt_ID_Port_S',2,1
Go
-- Test Data: poc2
If object_id('poc2') is not null
Drop table poc2
Go
Create table poc2(ID int,[Index] int,RndInput int,BadBack int,backtime datetime,NeedTime int)
Go
Insert into poc2
select 1,1,465654,1,'2008-05-20 00:00:00',300 union all
select 1,2,456645,1,'2008-05-20 00:00:00',200 union all
select 1,3,4554455,1,'2008-05-20 00:00:00',10 union all
select 1,4,4645,1,'2008-05-20 00:00:00',200 union all
select 1,1,00001,1,'2008-05-20 00:00:00',300 union all
select 1,2,00022,1,'2008-05-20 00:00:00',200 union all
select 1,3,00003,1,'2008-05-20 00:00:00',10 union all
select 1,4,00004,1,'2008-05-20 00:00:00',200
Go
--Start
declare @sql varchar(8000)
--set @sql = ''
select @sql=isnull(@sql+',','')+'max(case when a.[index]='+ltrim([index])+' then param end) [param'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badvalue end) [badvalue'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badtype end) [badtype'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badreturnvalue end) [badreturnvalue'+ltrim([index])+']
'
from ta group by [index]
exec('select a.id,rightvalue,c.backtime,'+ @sql + ' from ta a,pro b,poc2 c where c.[index] = a.[index] and a.id = b.id group by a.id,b.rightvalue,c.backtime')
--Result:
/*
id rightvalue backtime param1 badvalue1 badtype1 badreturnvalue1 param2 badvalue2 badtype2 badreturnvalue2 param3 badvalue3 badtype3 badreturnvalue3 param4 badvalue4 badtype4 badreturnvalue4
----------- ----------- ------------------------------------------------------ -------------------- ----------- ----------- --------------- -------------------- ----------- ----------- --------------- -------------------- ----------- ----------- --------------- -------------------- ----------- ----------- ---------------
1 1 2008-05-20 00:00:00.000 @Accounts 45645 1 1 @Pwd 456 2 1 @LoginIP 1131 1 1 @OutPutValue 8978 3 2
2 2 2008-05-20 00:00:00.000 @Accounts 4656 4 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 2 2008-05-20 00:00:00.000 @UserId 45645 5 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
11 1 2008-05-20 00:00:00.000 @UserID 456456 4 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
12 1 2008-05-20 00:00:00.000 @ServerID 45645 8 8 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
13 1 2008-05-20 00:00:00.000 @ServerID 456456 9 7 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL警告: 聚合或其它 SET 操作消除了空值。*/
--End
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(ID int,[index] int,param varchar(20),badvalue int,badtype int,badreturnvalue int)
Go
Insert into Ta
select 1,1,'@Accounts',45645,1,1 union all
select 1,2,'@Pwd',456,2,1 union all
select 1,3,'@LoginIP',1131,1,1 union all
select 1,4,'@OutPutValue',8978,3,2 union all
select 2,1,'@Accounts',4656,4,4
Go
-- Test Data: poc
If object_id('poc') is not null
Drop table poc
Go
Create table poc(ID int,name varchar(57),backtype int,rightvalue int)
Go
Insert into poc
select 1,'UP_UserInfo_Login',1,0 union all
select 2,'UP_UI_ID_OneUserInfo_S',2,1 Go
-- Test Data: poc2
If object_id('poc2') is not null
Drop table poc2
Go
Create table poc2(ID int,[Index] int,RndInput int,BadBack int,backtime datetime,NeedTime int)
Go
Insert into poc2
select 1,1,465654,1,'2008-05-20 00:00:00',300 union all
select 1,2,456645,1,'2008-05-20 00:00:00',200 union all
select 1,3,4554455,1,'2008-05-20 00:00:00',10 union all
select 1,4,4645,1,'2008-05-20 00:00:00',200 union all
select 2,1,00001,1,'2008-05-20 00:00:00',300 union all
select 2,2,00022,1,'2008-05-20 00:00:00',200 union all
select 2,3,00003,1,'2008-05-20 00:00:00',10 union all
select 2,4,00004,1,'2008-05-20 00:00:00',200
Go
--Start
declare @sql varchar(8000)
--set @sql = ''
select @sql=isnull(@sql+',','')+
'max(case when a.[index]='+ltrim([index])+' then RndInput end) [RndInput'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then backtype end) [backtype'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then BadBack end) [BadBack'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then param end) [param'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badvalue end) [badvalue'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badtype end) [badtype'+ltrim([index])+']
'+
',max(case when a.[index]='+ltrim([index])+' then badreturnvalue end) [badreturnvalue'+ltrim([index])+']
'
from ta group by [index]
exec('select a.id, b.rightvalue,convert(char(10),c.backtime,120) as backtime,'+ @sql + ' from ta a,poc b,poc2 c where c.[index] = a.[index] and a.id = b.id and a.id = c.id group by a.id,b.rightvalue,convert(char(10),c.backtime,120)')
--Result:
/*id rightvalue backtime RndInput1 backtype1 BadBack1 param1 badvalue1 badtype1 badreturnvalue1 RndInput2 backtype2 BadBack2 param2 badvalue2 badtype2 badreturnvalue2 RndInput3 backtype3 BadBack3 param3 badvalue3 badtype3 badreturnvalue3 RndInput4 backtype4 BadBack4 param4 badvalue4 badtype4 badreturnvalue4
----------- ----------- ---------- ----------- ----------- ----------- -------------------- ----------- ----------- --------------- ----------- ----------- ----------- -------------------- ----------- ----------- --------------- ----------- ----------- ----------- -------------------- ----------- ----------- --------------- ----------- ----------- ----------- -------------------- ----------- ----------- ---------------
1 0 2008-05-20 465654 1 1 @Accounts 45645 1 1 456645 1 1 @Pwd 456 2 1 4554455 1 1 @LoginIP 1131 1 1 4645 1 1 @OutPutValue 8978 3 2
2 1 2008-05-20 1 2 1 @Accounts 4656 4 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL警告: 聚合或其它 SET 操作消除了空值。*/
--End