Sqlserver 游标&存储过程&临时表混合使用实例
通过嵌套,根据表中记录的表名与列名到指定位置取值。
--声明存储过程 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 --执行存储过程