Sqlserver 游标&存储过程&临时表混合使用实例

概述通过嵌套，根据表中记录的表名与列名到指定位置取值。 --声明存储过程 ProPIMSif (exists (select * from sys.objects where name = ‘ProPIMS‘)) drop proc ProPIMSgo create procedure ProPIMS@dcsgroupinfo varchar(100),@dcsmcinfo var

通过嵌套，根据表中记录的表名与列名到指定位置取值。

--声明存储过程   ProPimsif (exists (select * from sys.objects where name = ‘ProPims‘))    drop proc ProPimsgo 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,@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.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,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,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,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 @dcsmcinfoEND--执行存储过程

总结

以上是内存溢出为你收集整理的Sqlserver 游标&存储过程&临时表混合使用实例全部内容，希望文章能够帮你解决Sqlserver 游标&存储过程&临时表混合使用实例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。