111,098
社区成员




ALTER PROCEDURE [dbo].[SqlCtRecord]
@tableName varchar(255) -- 表名
AS
if(object_id('temptable') is not null)----创建临时表
begin
drop table temptable
end
begin
exec('select * into temptable from (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from '+@tableName+' where id in (select max(ID) from '+@tableName+' as tb group by cid))t')
select tr1.*,'jxsname' as ac from (select JXSname from temptable group by JXSname having (count(JXSname)>1 and JXSname<>'' and JXSname is not null))t1
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by JXSname))tr1 on t1.JXSname = tr1.JXSname
union all select tr2.*,'name' as ac from (select ObjName from temptable group by ObjName having (count(ObjName)>1 and ObjName<>'' and ObjName is not null))t2
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjName))tr2 on t2.ObjName = tr2.ObjName
union all select tr3.*,'num' as ac from (select ObjNum from temptable group by ObjNum having (count(ObjNum)>1 and ObjNum<>'' and ObjNum is not null))t3
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjNum))tr3 on t3.ObjNum = tr3.ObjNum
union all select tr4.*,'ttnum' as ac from (select ttNum from temptable group by ttNum having (count(ttNum)>1 and ttNum<>'' and ttNum is not null))t4
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ttNum))tr4 on t4.ttNum = tr4.ttNum
union all select tr5.*,'ObjJGListNum' as ac from (select ObjJGListNum from temptable group by ObjJGListNum having (count(ObjJGListNum)>1 and ObjJGListNum<>'' and ObjJGListNum is not null))t5
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjJGListNum))tr5 on t5.ObjJGListNum = tr5.ObjJGListNum
end
drop table temptable --销毁临时表
在查询分析器中只要2S,在程序中就超时。。。。
ALTER PROCEDURE [dbo].[ERP_Report_Graph_P]
@ERPMenuID int,
@Graph int,
@Parameter nvarchar(100),
@PrimaryKeyValue nvarchar(max)
AS
BEGIN
declare @Str nvarchar(max),
@Host_Name nvarchar(50),
@ProcedureName nvarchar(100),
@TableName nvarchar(50),
@TimePrimaryKey nvarchar(50),
@Loop int,
@ProcedureID int,
@ERP_Report_Type int
select @Host_Name=b.[Host_Name],
@ProcedureID=b.ID,
@ProcedureName=b.U_Name,
@TableName=b.ERPTableName,
@TimePrimaryKey=b.ERPTableTimePrimaryKey,
@ERP_Report_Type=a.ERP_Report_Type
from ERP_Report_Menu_T a inner join
ERP_Procedure_T b
on b.ID=a.ERP_Procedure_ID and b.IsValid=1
where a.IsValid=1 and a.ID=@ERPMenuID
if @ERP_Report_Type<>3
begin
select '报表类型不是图表展示,系统报错!请与系统管理员联系解决...' [提示:]
return
end
set @Str='create table #table('
select @Str=@Str+(case U_Order
when 1 then ''
else ','
end)+
Column_Name+' '+Column_DataType
from ERP_Procedure_Column_T
where ERP_Procedure_ID=@ProcedureID
order by U_Order
set @Str=@Str+')insert #table '
if dbo.U_IsNull(@TableName)=1
select @Str=@Str+'exec '+
b.LocalURL+
'[Sys_CommonTable_DataSource_P] '+
@TableName+','+
@TimePrimaryKey+','+
@Parameter+','+
(case dbo.U_IsNull(@PrimaryKeyValue)
when 0 then ''''''
else ''''''+@PrimaryKeyValue+''''''
end)
from ERP_Report_Menu_T a inner join
ERP_Report_Dept_T b
on b.ID=a.ERP_Dept_ID
where a.ID=@ERPMenuID
else if dbo.U_IsNull(@Host_Name)=0
select @Str=@Str+'exec '+
@ProcedureName +' 1,' +
@Parameter+','+
convert(nvarchar(50),a.ERP_Dept_ID)
from ERP_Report_Menu_T a
where a.ID=@ERPMenuID
else
select @Str=@Str+'exec '+
d.LocalURL+
@Host_Name+' '+
@Parameter+
(case
when exists(select c.ID
from ERP_Procedure_Parameter_T c
where c.IsValid=1 and
c.ERP_Procedure_ID=@ProcedureID and
c.Parameter='@Where')
then ','''''
else ''
end)
from ERP_Report_Menu_T a inner join
ERP_Report_Dept_T d
on d.ID=a.ERP_Dept_ID
where a.ID=@ERPMenuID
set @Str=@Str+'select '
select @Str=@Str+(case U_Order
when 1 then ''
else ','
end)+'['+
Column_Name+
'] as ['+
MatchField+']'
from ERP_Procedure_Column_T
where U_Visible=1
order by U_Order
select @Str=@Str+' into #table1 from #table'+
(case (select Direction
from ERP_Report_Graph_T
where ID=@Graph)
when 1 then 'declare @Loop nvarchar(100),
@Order int
select distinct([sort]) as [TabPage],
U_Order
into #table2
from #table1
order by [U_Order]
select Tabpage,
row_number()over(order by U_Order,Tabpage) as [U_Order]
from #table2
declare _cursor cursor
for
select distinct([sort]),
U_Order
from #table1
order by [U_Order]
open _cursor
fetch next from _cursor into @Loop,@Order
while @@fetch_status=0
begin
select ordinate,
[sort],
abscissa
from #table1
where [sort]=@Loop
fetch next from _cursor into @Loop,@Order
end
close _cursor
deallocate _cursor'
when 2 then 'declare @Loop nvarchar(100)
select distinct([ordinate]) as [TabPage]
into #table2
from #table1
select Tabpage,
row_number()over(order by Tabpage) as [U_Order]
from #table2
declare _cursor cursor
for
select distinct([ordinate])
from #table1
open _cursor
fetch next from _cursor into @Loop
while @@fetch_status=0
begin
select [sort] as ordinate,ordinate as [sort],
abscissa
from #table1
where [ordinate]=@Loop
fetch next from _cursor into @Loop
end
close _cursor
deallocate _cursor'
else
'select ''单表'' [TabPage],
1 [U_Order]
select ordinate,
[sort],
abscissa
from #table1'
end)
-- print @Str
if dbo.U_IsNull(@Str)=1 exec sp_executesql @Str
-- return dbo.U_IsNull(@Str)
END