考虑调用时参数返回问题，因为原有接口会用到，所以需要2个返回表结构。
DataSet ds = run(Store Proceduce)
ds.Tables[0]:具体数据，
ds.Tables[1].Rows[0][0].ToString2().ToInt() ：数据总量，
原想可能会需要select into 什么表，后再return返回，结果发现select就能直接从exec中返回，比较简单。
这可能是做个存储过程中最让人省心的事情了。
最终如下：
ALTER PROCEDURE [dbo].[cal_etor_score_in_half_month] @user_id int,
@pageIndex int,
@pageSize int,
@orderby nvarchar(100),
@strWhere nvarchar(500)
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
DECLARE @sqlStr nvarchar(max)
DECLARE @sqlStr1 nvarchar(max)
DECLARE @sqlStr2 nvarchar(max)
DECLARE @sqlStr3 nvarchar(max)
DECLARE @sqlStr4 nvarchar(max)
DECLARE @isSys INT
--- {{{ use sys judge
SELECT @isSys=is_sys from by_user where id=@user_id;
IF @isSys!=1
BEGIN
set @strWhere=@strWhere + ' And etor_id in (select etor_id from by_user_etor where user_id='+ cast(@user_id as varchar(100)) +')'
END
--- use sys judge }}}
--{{{ page INDEX
IF @pageIndex < 1
BEGIN
set @pageIndex = 1
END
set @pageIndex = (@pageIndex - 1) * @pageSize + 1;
-- page index }}}
-- {{{ Step One ----
set @sqlStr1=N'
DECLARE @tb1 Table
(
etor_id int,
dtu_bianhao varchar(50),
create_time datetime,
etor_bianhao varchar(50),
weibao_user varchar(50),
weibao_userphone varchar(50),
weibao_manager varchar(50),
weibao_managerphone varchar(50),
fault_code varchar(50),
fault_desc varchar(500),
levels int,
sleep_starttime datetime,
sleep_endtime datetime,
etor_species_code varchar(10),
proj_name varchar(50),
proj_sleep_starttime datetime,
proj_sleep_endtime datetime,
organize_bianhao varchar(50)
)
insert into @tb1
SELECT
--a.id,
a.etor_id,
a.dtu_bianhao,
--a.master_code,
a.create_time,
b.bianhao AS etor_bianhao,
--b.dtu_id,
--b.etor_species_id,
--b.project_id,
--b.organize_id,
--b.dizhi_code,
--b.build_name,
--b.build_number,
--b.build_etorindex,
--b.dizhi_detail,
--b.zuobiao,
--b.zhuban_code,
--b.zhuban_version,
--b.zhuban_version_time,
--b.floorcount,
b.weibao_user,
b.weibao_userphone,
b.weibao_manager,
b.weibao_managerphone,
--b.weibao_interval,
--b.weibao_type,
--b.nianjian_time,
--b.use_place,
--b.brand_id,
--b.etor_model,
--b.product_model,
c.fault_code,
--c.fault_display_code,
c.fault_desc,
--c.is_alarm,
c.levels,
c.sleep_starttime,
c.sleep_endtime,
--d.caption AS etor_species_caption,
d.code AS etor_species_code,
--d.etor_type,
--d.remote_aspx,
--d.etor_default_img,
--d.mask_format,
table_proj.name AS proj_name,
table_proj.sleep_starttime AS proj_sleep_starttime,
table_proj.sleep_endtime AS proj_sleep_endtime,
table_org.bianhao as organize_bianhao
FROM
dbo.by_etor_tickets_fault AS a
INNER JOIN dbo.by_etor AS b ON a.etor_id = b.id
INNER JOIN dbo.by_etor_fault_config AS c ON a.master_code = c.fault_code AND b.etor_species_id = c.etor_species_id
INNER JOIN dbo.by_etor_species AS d ON b.etor_species_id = d.id
INNER JOIN dbo.by_project AS table_proj ON b.project_id = table_proj.id
LEFT JOIN dbo.by_organize AS table_org ON b.organize_id=table_org.id
--filter sleep time
where
(
( ISNULL(table_proj.sleep_starttime, '''') = '''' or ISNULL(table_proj.sleep_endtime, '''') ='''' )
OR
( ISNULL(table_proj.sleep_starttime, '''') != '''' and ISNULL(table_proj.sleep_endtime, '''') != ''''
AND
(
(convert(datetime,table_proj.sleep_starttime) < convert(datetime,table_proj.sleep_endtime)
AND convert(varchar(8),a.create_time,108) not between convert(datetime,table_proj.sleep_starttime) AND convert(datetime,table_proj.sleep_endtime)
)
OR
(convert(datetime,table_proj.sleep_starttime) > convert(datetime,table_proj.sleep_endtime)
AND (convert(varchar(8),a.create_time,108) between convert(datetime,table_proj.sleep_endtime) AND convert(datetime,table_proj.sleep_starttime))
)
)
)
)
AND
(
( ISNULL(c.sleep_starttime, '''') = '''' or ISNULL(c.sleep_endtime, '''') ='''' )
OR
( ISNULL(c.sleep_starttime, '''') != '''' and ISNULL(c.sleep_endtime, '''') != ''''
AND
(
(convert(datetime,c.sleep_starttime) < convert(datetime,c.sleep_endtime)
AND convert(varchar(8),a.create_time,108) not between convert(datetime,c.sleep_starttime) AND convert(datetime,c.sleep_endtime)
)
OR
(convert(datetime,c.sleep_starttime) > convert(datetime,c.sleep_endtime)
AND (convert(varchar(8),a.create_time,108) between convert(datetime,c.sleep_endtime) AND convert(datetime,c.sleep_starttime))
)
)
)
)
AND '+ @strWhere +';'
--end where ---
--- Step One }}} ----
--- {{{ Step Two ----
set @sqlStr2=N'
DECLARE @tb2 Table
(
etor_id int,
etor_bianhao varchar(50),
etor_species_code varchar(10),
dtu_bianhao varchar(50),
proj_name varchar(50),
organize_bianhao varchar(50),
weibao_user varchar(50),
weibao_userphone varchar(50),
--weibao_manager varchar(50),
--weibao_managerphone varchar(50),
fault_code varchar(50),
fault_desc varchar(500),
fault_score int
)
insert into @tb2
SELECT etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,weibao_userphone,fault_code,fault_desc,
sum( case when levels=0 then 20
when levels=1 then 10
when levels=2 then 6
when levels=3 then 4
when levels=4 then 2
when levels=5 then 1 --0.1~1
else 0 end) as fault_score
from @tb1
GROUP BY etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,weibao_userphone,fault_code,fault_desc
;'
--- Step Two }}} ----
--- {{{ Step Three ----
set @sqlStr3=N'
DECLARE @tb3 TABLE
(
rowid int,
etor_id int,
bianhao varchar(50),
etor_species_code varchar(10),
dtu_bianhao varchar(50),
proj_name varchar(50),
organize_bianhao varchar(50),
weibao_user varchar(50),
weibao_userphone varchar(50),
fault_score_total int,
fault_code1 varchar(50),
fault_des1 varchar(500),
fault_score1 int,
fault_code2 varchar(50),
fault_des2 varchar(500),
fault_score2 int,
fault_code3 varchar(50),
fault_des3 varchar(500),
fault_score3 int
)
insert into @tb3
SELECT row_number() over (order by '+ @orderby +' ) as rowid,A.etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,weibao_userphone,fault_score_total,fault_code1,fault_des1,fault_score1,fault_code2,fault_des2,fault_score2,fault_code3,fault_des3,fault_score3 from
(
SELECT etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,weibao_userphone,[1] as fault_score1, [2] as fault_score2, [3] as fault_score3
from
(
select a.etor_id,a.etor_bianhao,a.etor_species_code,a.dtu_bianhao,a.proj_name,a.organize_bianhao,a.weibao_user,a.weibao_userphone,a.fault_score,a.fautl_score_range
from (select t.*,row_number()over(partition by etor_id order by fault_score desc) as fautl_score_range from @tb2 t) a
where fautl_score_range<=3
)
AS SourceTable
PIVOT
(
MAX(fault_score) FOR fautl_score_range IN ([1], [2], [3])
) AS PivotTable
)A
LEFT JOIN
(
SELECT etor_id,[1] as fault_des1, [2] as fault_des2, [3] as fault_des3
from
(
select a.etor_id,a.fault_desc,a.fautl_score_range
from (select t.*,row_number()over(partition by etor_id order by fault_score desc) as fautl_score_range from @tb2 t) a
where fautl_score_range<=3
)
AS SourceTable2
PIVOT
(
MAX(fault_desc) FOR fautl_score_range IN ([1], [2], [3])
) AS PivotTable2
)B on A.etor_id=B.etor_id
LEFT JOIN
(
SELECT etor_id,[1] as fault_code1, [2] as fault_code2, [3] as fault_code3
FROM
(
select a.etor_id,a.fault_code,a.fautl_score_range
from (select t.*,row_number()over(partition by etor_id order by fault_score desc) as fautl_score_range FROM @tb2 t) a
where fautl_score_range<=3
)
AS SourceTable2
PIVOT
(
MAX(fault_code) FOR fautl_score_range IN ([1], [2], [3])
) AS PivotTable2
)C on A.etor_id=C.etor_id
LEFT JOIN
(
SELECT etor_id,sum(fault_score) as fault_score_total from @tb2 GROUP BY etor_id
)D on A.etor_id=D.etor_id
;'
--- Step Three }}} ----
--- {{{ Step Four ----
set @sqlStr4=N' select * from @tb3 '
+' where rowid between '+ cast(@pageIndex as varchar(100))+' and '+cast((@pageIndex+@pageSize-1) as varchar(100))
+';select count(0) from @tb3 '
--- Step Four }}} ----
--+';select * from @tb2'
set @sqlStr=@sqlStr1+@sqlStr2+@sqlStr3+@sqlStr4
print len(@sqlStr1)
print len(@sqlStr2)
print len(@sqlStr3)
print len(@sqlStr)
exec(@sqlStr);
--exec ('select count(0) from ('+@sql+') t ');
--select * from @tb3
END