USE [beone]
GO
/****** 对象: StoredProcedure [dbo].[empCusInd] 脚本日期: 03/04/2011 16:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: GM
-- Create date: 2011-2-21
-- Description: 客户行业统计
-- =============================================
CREATE PROCEDURE [dbo].[empCusInd](
@sqlAppend varchar(8000)
)
AS
BEGIN
--申明字符串变量,以供动态拼装
declare @sql varchar(max),@sql1 varchar(8000)
--将客户行业列放入临时表
set @sql1='select typ_name as sta_name into ##tempCusInd
from cus_cor_cus left join type_list on cor_ind_id=typ_id where cor_isdelete=''1'' group by typ_name order by typ_name desc'
if object_id('tempdb..##tempCusInd') is not null
begin
drop table ##tempCusInd
end
exec(@sql1)--拼装SQL命令
set @sql = 'select se_no,max(se_code),max(se_name) as head'
--将客户行业旋转为表头
select @sql = @sql +
case when sta_name is not null then
', sum(case typ_name when '''+sta_name+''' then 1 else 0 end) ['+sta_name+']'
else
', sum(case when typ_name is null then 1 else 0 end) as 未选择'
end from (select sta_name from ##tempCusInd)as a
--加上合计
select @sql = @sql+',
count(cor_code)as 合计
from cus_cor_cus inner join sal_emp on cor_se_no=se_no left join type_list on cor_ind_id=typ_id
where cor_isdelete=''1'' ' + @sqlAppend
+ ' group by se_no with rollup'
--print(@sql)
exec(@sql)
END
还请大神们多多指教!有哪些注意事项,谢谢存储过程 sqlmysql
GO
/****** 对象: StoredProcedure [dbo].[empCusInd] 脚本日期: 03/04/2011 16:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: GM
-- Create date: 2011-2-21
-- Description: 客户行业统计
-- =============================================
CREATE PROCEDURE [dbo].[empCusInd](
@sqlAppend varchar(8000)
)
AS
BEGIN
--申明字符串变量,以供动态拼装
declare @sql varchar(max),@sql1 varchar(8000)
--将客户行业列放入临时表
set @sql1='select typ_name as sta_name into ##tempCusInd
from cus_cor_cus left join type_list on cor_ind_id=typ_id where cor_isdelete=''1'' group by typ_name order by typ_name desc'
if object_id('tempdb..##tempCusInd') is not null
begin
drop table ##tempCusInd
end
exec(@sql1)--拼装SQL命令
set @sql = 'select se_no,max(se_code),max(se_name) as head'
--将客户行业旋转为表头
select @sql = @sql +
case when sta_name is not null then
', sum(case typ_name when '''+sta_name+''' then 1 else 0 end) ['+sta_name+']'
else
', sum(case when typ_name is null then 1 else 0 end) as 未选择'
end from (select sta_name from ##tempCusInd)as a
--加上合计
select @sql = @sql+',
count(cor_code)as 合计
from cus_cor_cus inner join sal_emp on cor_se_no=se_no left join type_list on cor_ind_id=typ_id
where cor_isdelete=''1'' ' + @sqlAppend
+ ' group by se_no with rollup'
--print(@sql)
exec(@sql)
END
还请大神们多多指教!有哪些注意事项,谢谢存储过程 sqlmysql
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货