通过嵌套，根据表中记录的表名与列名到指定位置取值。
--声明存储过程 ProPIMS
if (exists (select * from sys.objects where name = 'ProPIMS'))
drop proc ProPIMS
go
create procedure ProPIMS
@dcsgroupinfo varchar(100),@dcsmcinfo varchar (150)
AS
BEGIN declare @dcsgroup varchar(50),@mc varchar(50),@dw varchar(50),@tablename varchar(50),@columnname varchar(50),@Columncount int;
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##dcs_tablevalue'))
drop table ##dcs_tablevalue;
create table ##dcs_tablevalue
(
tablename varchar(50),
columnname varchar(50),
value varchar(50)
)
declare @charsql varchar(500)
if cursor_status('global','mycursor')=-3 and cursor_status('local','mycursor')=-3
declare mycursor cursor for
select distinct dcsgroup,mc,dw,tablename,columnname
from
(
select a.dcsgroup,a.mc,a.dw,b.tablename,a.columnname
from dcsdata.dcsdatainf a
left join dcsdata.dcsgroup b on a.dcsgroup = b.groupname
)total
open mycursor
fetch next from mycursor into @dcsgroup,@mc,@dw,@tablename,@columnname
while(@@FETCH_STATUS = 0)
begin
set @Columncount = 0;
SELECT @Columncount = count(*) from syscolumns where name= @columnname and id=object_id(@tablename);
if(@Columncount = 1)
set @charsql = 'insert into ##dcs_tablevalue select top 1 '''+@tablename+''','''+@columnname+''',(select top 1 ' +@columnname +' from '+ @tablename+');'
exec(@charsql);
fetch next from mycursor into @dcsgroup,@mc,@dw,@tablename,@columnname
end
close mycursor if(@dcsgroupinfo is not null and @dcsgroupinfo != '' and (@dcsmcinfo is null or @dcsmcinfo = ''))--取一整个模块的数据
--查询结果集
select distinct total.dcsgroup,total.mc,total.dw,##dcs_tablevalue.value,##dcs_tablevalue.tablename,##dcs_tablevalue.columnname
from ##dcs_tablevalue
left join
(
select a.dcsgroup,a.mc,a.dw,b.tablename,a.columnname
from dcsdata.dcsdatainf a
left join dcsdata.dcsgroup b on a.dcsgroup = b.groupname
)total on total.tablename = ##dcs_tablevalue.tablename and total.columnname = ##dcs_tablevalue.columnname
where total.dcsgroup = @dcsgroupinfo
else if (@dcsmcinfo = 'all')--取39个全厂概貌
--查询结果集
select distinct total.dcsgroup,total.mc,total.dw,##dcs_tablevalue.value,##dcs_tablevalue.tablename,##dcs_tablevalue.columnname
from ##dcs_tablevalue
left join
(
select a.dcsgroup,a.mc,a.dw,b.tablename,a.columnname
from dcsdata.dcsdatainf a
left join dcsdata.dcsgroup b on a.dcsgroup = b.groupname
)total on total.tablename = ##dcs_tablevalue.tablename and total.columnname = ##dcs_tablevalue.columnname
where mc in
('中压蒸汽压力','低压蒸汽压力','下段气总管压力','中段气总管压力','清洗气总管压力',
'滤过真空度','循环水压力','下段气总流量','中段气总流量','清洗气总流量',
'重碱皮带秤','真空淡液塔淡液流量','热母液总管流量','蒸馏灰乳总流量','取出液总流量',
'淡氨盐水总流量','冷母液流量','碳化尾气总管压力','热氨盐水桶液位','冷氨盐水桶液位',
'中和水贮桶液位','1#热母液液位','2#热母液液位','1#冷母液桶液位','2#冷母液桶液位',
'I组中和水温度','II组中和水温度','III组中和水温度','滤过吹风压力','循环水温度',
'冷冻水温度','氨盐水总管温度','重碱精盐水总流量','氨气总管温度','精盐水温度',
'淡液塔中部温度','去盐水循环水温度','滤过洗水温度','滤碱机总洗水流量')
and dcsgroup in ('重碱碳滤DCS','煅烧DCS')
else if(@dcsgroupinfo is null or @dcsgroupinfo = '')--取所有数据
--查询结果集
select distinct total.dcsgroup,total.mc,total.dw,##dcs_tablevalue.value,##dcs_tablevalue.tablename,##dcs_tablevalue.columnname
from ##dcs_tablevalue
left join
(
select a.dcsgroup,a.mc,a.dw,b.tablename,a.columnname
from dcsdata.dcsdatainf a
left join dcsdata.dcsgroup b on a.dcsgroup = b.groupname
)total on total.tablename = ##dcs_tablevalue.tablename and total.columnname = ##dcs_tablevalue.columnname
else--取相关模块相关子模块的数据
--查询结果集
select distinct total.dcsgroup,total.mc,total.dw,##dcs_tablevalue.value,##dcs_tablevalue.tablename,##dcs_tablevalue.columnname
from ##dcs_tablevalue
left join
(
select a.dcsgroup,a.mc,a.dw,b.tablename,a.columnname
from dcsdata.dcsdatainf a
left join dcsdata.dcsgroup b on a.dcsgroup = b.groupname
)total on total.tablename = ##dcs_tablevalue.tablename and total.columnname = ##dcs_tablevalue.columnname
where total.dcsgroup = @dcsgroupinfo and total.mc like @dcsmcinfo
END
--执行存储过程