前文已经通过视图把基本需求实现了，现在要把他们合成一个存储过程。原以为会比较简单的实现，把这几个设计视图的代码合在一起就可以了。
实际上，我们的需求还要添加一些条件限制筛选，这也是为什么不直接用视图的原因。
条件1：通过用户筛选。
条件2：通过时间筛选。通过类型筛选等等。
这些条件需要通过参数传递进来。下面是最早的版本，依然使用的是临时表。
ALTER PROCEDURE [dbo].[cal_etor_score_in_half_month]
-- @user_id int,
-- @pageSize int,
-- @pageIndex int,
@strWhere nvarchar(max)
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
if OBJECT_ID('tempdb..#etor_fault') is not null
drop table #etor_fault
-- {{{ Step One ----
--EXEC sp_executesql N'
DECLARE @tb1 Table
(
Id int,
Name varchar(20),
Age int
)
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
into #etor_fault
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
LEFT 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 }}} ----
if OBJECT_ID('tempdb..#etor_fault_score') is not null
drop table #etor_fault_score
--- {{{ Step Two ----
SELECT etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,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
into #etor_fault_score
from #etor_fault
where etor_id<100
GROUP BY etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,fault_code,fault_desc
--- Step Two }}} ----
--- {{{ Step Three ----
SELECT A.etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,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,[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.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 #etor_fault_score 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 #etor_fault_score 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 #etor_fault_score 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
#etor_fault_score
GROUP BY etor_id
)D on A.etor_id=D.etor_id
--- Step Three }}} ----
END
问题1：
使用上面实现方法遇到的问题是，传入的参数如何作为where的条件筛选？
1）通过谷歌百度发现只能通过exec sql_str 这种方法实现，而使用这种方法，无法在其中继续使用临时表，只能设计表，并使用表变量。增加了大量设计表的工作。
2）原文中的单引号，必须用2个单引号代替
3）即便如此，全文数量长度限制，一个nvchar(max)只有4000字长度，需要分段实现。
解决以上问题后最终如下。
ALTER PROCEDURE [dbo].[cal_etor_score_in_half_month] @user_id int,-- @pageSize int,-- @pageIndex int, @strWhere nvarchar(500)ASBEGIN -- 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 @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 }}}-- {{{ 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 @tb1SELECT--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_bianhaoFROMdbo.by_etor_tickets_fault AS aINNER JOIN dbo.by_etor AS b ON a.etor_id = b.idINNER JOIN dbo.by_etor_fault_config AS c ON a.master_code = c.fault_code AND b.etor_species_id = c.etor_species_idINNER JOIN dbo.by_etor_species AS d ON b.etor_species_id = d.idINNER JOIN dbo.by_project AS table_proj ON b.project_id = table_proj.idLEFT 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 @tb2SELECT etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,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 @tb1GROUP BY etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,fault_code,fault_desc;'--- Step Two }}} ------- {{{ Step Three ----set @sqlStr3=N'SELECT A.etor_id,etor_bianhao,etor_species_code,dtu_bianhao,proj_name,organize_bianhao,weibao_user,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,[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.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)ALEFT 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_idLEFT 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_idLEFT 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 }}} ------+';select * from @tb2'set @sqlStr=@sqlStr1+@sqlStr2+@sqlStr3print len(@sqlStr1)print len(@sqlStr2)print len(@sqlStr3)print len(@sqlStr)exec(@sqlStr)END
。。。