数据库的存储过程设计(2)

前文已经通过视图把基本需求实现了，现在要把他们合成一个存储过程。原以为会比较简单的实现，把这几个设计视图的代码合在一起就可以了。

实际上，我们的需求还要添加一些条件限制筛选，这也是为什么不直接用视图的原因。

条件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)

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 @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 @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,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,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
)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 }}} ----

--+';select * from @tb2'

set @sqlStr=@sqlStr1+@sqlStr2+@sqlStr3
print len(@sqlStr1)
print len(@sqlStr2)
print len(@sqlStr3)
print len(@sqlStr)

exec(@sqlStr)

END

。。。