[code=SQL] CREATE PROCEDURE [dbo].[sp_Data_Select] -- Add the parameters for the stored procedure here @DtBegin varchar(20),@DtEnd varchar(20),@Barn varchar(10),@Row varchar(5),@Level int,@Point int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; create table #GrainInfo ( Barn varchar(10), --仓库编号 Row varchar(5), --列编号 PLevel int, --层编号 Point int, --点编号 Temperature varchar(10), --温度数值 Humidity varchar(10), --湿度数值 Dt varchar(20), --日期时间 ) create table #temp ( Barn varchar(10), --仓库编号 Row varchar(5), --列编号 PLevel int, --层编号 Point int, --点编号 Temperature varchar(10), --温度数值 Humidity varchar(10), --湿度数值 Dt varchar(20), --日期时间 ) declare @DtFrom varchar(20),@DtTo varchar(20) declare @x int,@y int, @z int,@i int declare @xx int,@yy int, @zz int declare @avgtmpt varchar(20),@maxtmpt varchar(20) if substring(rtrim(@Barn),1,1)<>'P' begin if rtrim(@DtBegin)='' and rtrim(@DtEnd)='' begin select top 1 @DtFrom=left(rtrim(Dt),16),@DtTo=@DtFrom from GrainInfo where Barn=rtrim(@Barn) and Re='' and Row='01' order by Dt Desc if(rtrim(@Row)<>'') begin if(rtrim(@Level)<>'0') begin if(rtrim(@Point)<>'0') begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level) and Point=@Point and left(rtrim(Dt),16)=@DtFrom --and Row in('01','02','03','04','05','06') order by Barn,Row,PLevel,Point end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level) and left(rtrim(Dt),16)=@DtFrom order by Barn,Row,PLevel,Point end end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and left(rtrim(Dt),16)=@DtFrom order by Barn,Row,PLevel,Point end end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo where Barn=rtrim(@Barn) and left(rtrim(Dt),16)=@DtFrom order by Barn,Row,PLevel,Point end end else begin select top 1 @DtFrom=left(rtrim(Dt),16),@DtTo=@DtFrom from GrainInfo where Barn=rtrim(@Barn) and Re='' and Dt between @DtBegin and @DtEnd order by Dt Desc if(rtrim(@Row)<>'') begin if(rtrim(@Level)<>'0') begin if(rtrim(@Point)<>'0') begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level) and Point=@Point and left(rtrim(Dt),16)between @DtFrom and @DtTo order by Barn,Row,PLevel,Point end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level) and left(rtrim(Dt),16)between @DtFrom and @DtTo order by Barn,Row,PLevel,Point end end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Barn=rtrim(@Barn) and Row=rtrim(@Row) and left(rtrim(Dt),16)between @DtFrom and @DtTo order by Barn,Row,PLevel,Point end end else begin insert #GrainInfo select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Barn=rtrim(@Barn) and left(rtrim(Dt),16)between @DtFrom and @DtTo order by Barn,Row,PLevel,Point end end insert #GrainInfo select top 2 Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Re='In' and Barn=@Barn order by Dt Desc if exists (select PAdress from GrainInfo where Re='Name' and Barn=@Barn) begin insert #temp select distinct b.PAdress,a.Row,a.PLevel,a.Point,a.Temperature,a.Humidity,left(a.Dt,16) as Dt from #GrainInfo a,GrainInfo b where b.Barn=@Barn and b.Re='Name' end else begin insert #temp select distinct @Barn,Row,PLevel,Point,Temperature,Humidity,left(Dt,16) as Dt from #GrainInfo end declare @Str varchar(20) select @Str=Info from GrainSetting where Kind='Points' and Barn=@Barn
set @xx=convert(int,substring(@Str,1,charindex(',',@Str)-1)) set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str)) set @yy=convert(int,substring(@Str,1,charindex(',',@Str)-1)) set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str)) set @zz=convert(int,@Str)--没发全……接下帖
--接上半部 create table #result ( Surface varchar(20), PLevel varchar(20), a varchar(20), b varchar(20), c varchar(20), d varchar(20), e varchar(20), f varchar(20), g varchar(20), h varchar(20), i varchar(20), j varchar(20), k varchar(20), l varchar(20), avgtmpt varchar(20), maxtmpt varchar(20) ) create table #result2 ( Surface varchar(20), PLevel varchar(20), a varchar(20), b varchar(20), c varchar(20), d varchar(20), e varchar(20), f varchar(20), g varchar(20), h varchar(20), i varchar(20), j varchar(20), k varchar(20), l varchar(20), avgtmpt varchar(20), maxtmpt varchar(20) ) set @z=1 WHILE @z<@zz BEGIN set @y=1 WHILE @y<@yy begin declare @strx varchar(10) if @y=1 begin set @strx='上层' end else if @y=2 begin set @strx='中上层' end else if @y=3 begin set @strx='中下层' end else if @y=4 begin set @strx='下层' end -- if @Barn in('1','2','3','4','5','6','7','8','9','10') -- begin insert #result select @z,@y,Temperature,'0','0','0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='01'and PLevel=@y and Point=@z insert #result select @z,@y,'0',Temperature,'0','0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='02'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0',Temperature,'0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='03'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0',Temperature,'0','0','0','0','0','0','0','0','0','0' from #temp where Row='04'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0',Temperature,'0','0','0','0','0','0','0','0','0' from #temp where Row='05'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0',Temperature,'0','0','0','0','0','0','0','0' from #temp where Row='06'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0',Temperature,'0','0','0','0','0','0','0' from #temp where Row='07'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0','0',Temperature,'0','0','0','0','0','0' from #temp where Row='08'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0','0','0',Temperature,'0','0','0','0','0' from #temp where Row='09'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0','0','0','0',Temperature,'0','0','0','0' from #temp where Row='10'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0','0','0','0','0',Temperature,'0','0','0' from #temp where Row='11'and PLevel=@y and Point=@z insert #result select @z,@y,'0','0','0','0','0','0','0','0','0','0','0',Temperature,'0','0' from #temp where Row='12'and PLevel=@y and Point=@z --select * from #result -- declare @avgtmpt varchar(20),@maxtmpt varchar(20) select @avgtmpt=convert(varchar,round(avg(convert(float,Temperature)),2),20) from #temp where PLevel=@y and Point=@z select @maxtmpt=convert(varchar,max(convert(float,Temperature)),20) from #temp where PLevel=@y and Point=@z insert #result2 select Surface,PLevel,sum(round(convert(float,a),2)) as a,sum(round(convert(float,b),2)) as b,sum(round(convert(float,c),2)) as c,sum(round(convert(float,d),2)) as d,sum(round(convert(float,e),2)) as e,sum(round(convert(float,f),2)) as f,sum(round(convert(float,g),2)) as g,sum(round(convert(float,h),2)) as h,sum(round(convert(float,i),2)) as i,sum(round(convert(float,j),2)) as j,sum(round(convert(float,k),2)) as k,sum(round(convert(float,l),2)) as l,@avgtmpt,@maxtmpt from #result group by Surface,PLevel
delete #result set @y=@y+1 END set @z=@z+1 END--select * from #result2 --select * from #temp update #result2 set a=a+'.0' where CHARINDEX('.',a)=0 and (len(rtrim(a))>0 and len(rtrim(a))<4) and isnumeric(a)=1 update #result2 set b=b+'.0' where CHARINDEX('.',b)=0 and (len(rtrim(b))>0 and len(rtrim(b))<4) and isnumeric(b)=1 update #result2 set c=c+'.0' where CHARINDEX('.',c)=0 and (len(rtrim(c))>0 and len(rtrim(c))<4) and isnumeric(c)=1 update #result2 set d=d+'.0' where CHARINDEX('.',d)=0 and (len(rtrim(d))>0 and len(rtrim(d))<4) and isnumeric(d)=1 update #result2 set e=e+'.0' where CHARINDEX('.',e)=0 and (len(rtrim(e))>0 and len(rtrim(e))<4) and isnumeric(e)=1 update #result2 set f=f+'.0' where CHARINDEX('.',f)=0 and (len(rtrim(f))>0 and len(rtrim(f))<4) and isnumeric(f)=1 update #result2 set g=g+'.0' where CHARINDEX('.',g)=0 and (len(rtrim(g))>0 and len(rtrim(g))<4) and isnumeric(g)=1 update #result2 set h=h+'.0' where CHARINDEX('.',h)=0 and (len(rtrim(h))>0 and len(rtrim(h))<4) and isnumeric(h)=1 update #result2 set i=i+'.0' where CHARINDEX('.',i)=0 and (len(rtrim(i))>0 and len(rtrim(i))<4) and isnumeric(i)=1 update #result2 set j=j+'.0' where CHARINDEX('.',j)=0 and (len(rtrim(j))>0 and len(rtrim(j))<4) and isnumeric(j)=1 update #result2 set k=k+'.0' where CHARINDEX('.',k)=0 and (len(rtrim(k))>0 and len(rtrim(k))<4) and isnumeric(k)=1 update #result2 set l=l+'.0' where CHARINDEX('.',l)=0 and (len(rtrim(l))>0 and len(rtrim(l))<4) and isnumeric(l)=1 update #result2 set avgtmpt=avgtmpt+'.0' where CHARINDEX('.',avgtmpt)=0 and (len(rtrim(avgtmpt))>0 and len(rtrim(avgtmpt))<4) and isnumeric(avgtmpt)=1 update #result2 set maxtmpt=maxtmpt+'.0' where CHARINDEX('.',maxtmpt)=0 and (len(rtrim(maxtmpt))>0 and len(rtrim(maxtmpt))<4) and isnumeric(maxtmpt)=1 select * from #result2 where a is not null end else begin select 0 endEndexec sp_Data_Select '','','P9A','',0,0
//调用部分的代码 SqlConnection conn = new SqlConnection(strCon); conn.Open(); string str = "sp_Data_Select"; SqlCommand cmd = new SqlCommand(str, conn); cmd.CommandType = CommandType.StoredProcedure; if (chkBoxSelect.Checked) { dtb = ""; dte = ""; Row = ""; Level = "0"; Point = "0"; } SqlParameter sp = new SqlParameter("@DtBegin", SqlDbType.VarChar, 20); sp.Value = dtb; cmd.Parameters.Add(sp); sp = new SqlParameter("@DtEnd", SqlDbType.VarChar, 20); sp.Value = dte; cmd.Parameters.Add(sp); sp = new SqlParameter("@Barn", SqlDbType.VarChar, 10); sp.Value = BarnSel; cmd.Parameters.Add(sp); sp = new SqlParameter("@Row", SqlDbType.VarChar, 5); sp.Value = Row; cmd.Parameters.Add(sp); sp = new SqlParameter("@Level", SqlDbType.Int); sp.Value = int.Parse(Level); cmd.Parameters.Add(sp); sp = new SqlParameter("@Point", SqlDbType.Int); sp.Value = int.Parse(Point); cmd.Parameters.Add(sp); //SqlDataReader sqlReader = cmd.ExecuteReader(); try { if (DataGridSelect.RowCount > 1) { foreach (DataGridViewRow rr in DataGridSelect.Rows) { DataGridSelect.Rows.Clear(); } SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); dataAdapter.Fill(ds); this.DataGridSelect.DataSource = ds.Tables[0]; for (int i = 0; i < DataGridSelect.Columns.Count; i++) { DataGridSelect.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } dataAdapter.Close();
create table #GrainInfo ( Barn varchar(10), --仓库编号 Row varchar(5), --列编号 PLevel int, --层编号 Point int, --点编号 Temperature varchar(10), --温度数值 Humidity varchar(10), --湿度数值 Dt varchar(20), --日期时间 )create table #temp ( Barn varchar(10), --仓库编号 Row varchar(5), --列编号 PLevel int, --层编号 Point int, --点编号 Temperature varchar(10), --温度数值 Humidity varchar(10), --湿度数值 Dt varchar(20), --日期时间 )
set @xx=convert(int,substring(@Str,1,charindex(',',@Str)-1)) set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str)) set @yy=convert(int,substring(@Str,1,charindex(',',@Str)-1)) set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str)) set @zz=convert(int,@Str) PRINT @xx PRINT @yy --加上這一段看看賦值結果是否正確
代码多的让人激动,问题我也看不出 不过如果如下规律成立的话,你可以改写下代码 insert #result select @z,@y, case when Row='01' then Temperature else '0' end, case when Row='02' then Temperature else '0' end, case when Row='03' then Temperature else '0' end, case when Row='04' then Temperature else '0' end, case when Row='05' then Temperature else '0' end, case when Row='06' then Temperature else '0' end, case when Row='07' then Temperature else '0' end, case when Row='08' then Temperature else '0' end, case when Row='09' then Temperature else '0' end, case when Row='10' then Temperature else '0' end, case when Row='11' then Temperature else '0' end, case when Row='12' then Temperature else '0' end, '0','0' from #temp where PLevel=@y and Point=@z
恩,我已经把赋值语句改成了下面这样写死的,可是@yy=5 @zz=7时刚刚是可以运行的,然后我改成5,8的时候就报错,但是我再改回5,7也挂了……我真TM快疯了…… set @xx=11 set @yy=5 set @zz=7
1.先不要用程序执行 2.调用exec sp_Data_Select '','','P9A','',0,0 结果是啥 3.查看select Info from GrainSetting where Kind='Points' and Barn='P9A' 的结果
CREATE PROCEDURE [dbo].[sp_Data_Select]
-- Add the parameters for the stored procedure here
@DtBegin varchar(20),@DtEnd varchar(20),@Barn varchar(10),@Row varchar(5),@Level int,@Point int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; create table #GrainInfo
(
Barn varchar(10), --仓库编号
Row varchar(5), --列编号
PLevel int, --层编号
Point int, --点编号
Temperature varchar(10), --温度数值
Humidity varchar(10), --湿度数值
Dt varchar(20), --日期时间
) create table #temp
(
Barn varchar(10), --仓库编号
Row varchar(5), --列编号
PLevel int, --层编号
Point int, --点编号
Temperature varchar(10), --温度数值
Humidity varchar(10), --湿度数值
Dt varchar(20), --日期时间
) declare @DtFrom varchar(20),@DtTo varchar(20)
declare @x int,@y int, @z int,@i int
declare @xx int,@yy int, @zz int
declare @avgtmpt varchar(20),@maxtmpt varchar(20) if substring(rtrim(@Barn),1,1)<>'P'
begin if rtrim(@DtBegin)='' and rtrim(@DtEnd)=''
begin
select top 1 @DtFrom=left(rtrim(Dt),16),@DtTo=@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and Re='' and Row='01' order by Dt Desc if(rtrim(@Row)<>'')
begin
if(rtrim(@Level)<>'0')
begin
if(rtrim(@Point)<>'0')
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level)
and Point=@Point and left(rtrim(Dt),16)=@DtFrom --and Row in('01','02','03','04','05','06')
order by Barn,Row,PLevel,Point
end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level)
and left(rtrim(Dt),16)=@DtFrom order by Barn,Row,PLevel,Point
end
end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and left(rtrim(Dt),16)=@DtFrom
order by Barn,Row,PLevel,Point
end
end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and left(rtrim(Dt),16)=@DtFrom
order by Barn,Row,PLevel,Point
end
end
else
begin
select top 1 @DtFrom=left(rtrim(Dt),16),@DtTo=@DtFrom from GrainInfo
where Barn=rtrim(@Barn) and Re='' and Dt between @DtBegin and @DtEnd
order by Dt Desc if(rtrim(@Row)<>'')
begin
if(rtrim(@Level)<>'0')
begin
if(rtrim(@Point)<>'0')
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level)
and Point=@Point and left(rtrim(Dt),16)between @DtFrom and @DtTo
order by Barn,Row,PLevel,Point end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and PLevel=rtrim(@Level)
and left(rtrim(Dt),16)between @DtFrom and @DtTo
order by Barn,Row,PLevel,Point
end
end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo
where Barn=rtrim(@Barn) and Row=rtrim(@Row) and left(rtrim(Dt),16)between @DtFrom and @DtTo
order by Barn,Row,PLevel,Point
end
end
else
begin
insert #GrainInfo
select distinct Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo
where Barn=rtrim(@Barn) and left(rtrim(Dt),16)between @DtFrom and @DtTo
order by Barn,Row,PLevel,Point
end
end insert #GrainInfo
select top 2 Barn,Row,PLevel,Point,Temperature,Humidity,Dt from GrainInfo where Re='In' and Barn=@Barn order by Dt Desc
if exists (select PAdress from GrainInfo where Re='Name' and Barn=@Barn)
begin
insert #temp
select distinct b.PAdress,a.Row,a.PLevel,a.Point,a.Temperature,a.Humidity,left(a.Dt,16) as Dt
from #GrainInfo a,GrainInfo b where b.Barn=@Barn and b.Re='Name'
end
else
begin
insert #temp
select distinct @Barn,Row,PLevel,Point,Temperature,Humidity,left(Dt,16) as Dt from #GrainInfo
end declare @Str varchar(20)
select @Str=Info from GrainSetting where Kind='Points' and Barn=@Barn
set @xx=convert(int,substring(@Str,1,charindex(',',@Str)-1))
set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str))
set @yy=convert(int,substring(@Str,1,charindex(',',@Str)-1))
set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str))
set @zz=convert(int,@Str)--没发全……接下帖
(
Surface varchar(20),
PLevel varchar(20),
a varchar(20),
b varchar(20),
c varchar(20),
d varchar(20),
e varchar(20),
f varchar(20),
g varchar(20),
h varchar(20),
i varchar(20),
j varchar(20),
k varchar(20),
l varchar(20),
avgtmpt varchar(20),
maxtmpt varchar(20)
) create table #result2
(
Surface varchar(20),
PLevel varchar(20),
a varchar(20),
b varchar(20),
c varchar(20),
d varchar(20),
e varchar(20),
f varchar(20),
g varchar(20),
h varchar(20),
i varchar(20),
j varchar(20),
k varchar(20),
l varchar(20),
avgtmpt varchar(20),
maxtmpt varchar(20)
) set @z=1
WHILE @z<@zz
BEGIN
set @y=1
WHILE @y<@yy
begin
declare @strx varchar(10)
if @y=1
begin
set @strx='上层'
end
else if @y=2
begin
set @strx='中上层'
end
else if @y=3
begin
set @strx='中下层'
end
else if @y=4
begin
set @strx='下层'
end
-- if @Barn in('1','2','3','4','5','6','7','8','9','10')
-- begin
insert #result
select @z,@y,Temperature,'0','0','0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='01'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0',Temperature,'0','0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='02'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0',Temperature,'0','0','0','0','0','0','0','0','0','0','0' from #temp where Row='03'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0',Temperature,'0','0','0','0','0','0','0','0','0','0' from #temp where Row='04'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0',Temperature,'0','0','0','0','0','0','0','0','0' from #temp where Row='05'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0',Temperature,'0','0','0','0','0','0','0','0' from #temp where Row='06'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0',Temperature,'0','0','0','0','0','0','0' from #temp where Row='07'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0','0',Temperature,'0','0','0','0','0','0' from #temp where Row='08'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0','0','0',Temperature,'0','0','0','0','0' from #temp where Row='09'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0','0','0','0',Temperature,'0','0','0','0' from #temp where Row='10'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0','0','0','0','0',Temperature,'0','0','0' from #temp where Row='11'and PLevel=@y and Point=@z
insert #result
select @z,@y,'0','0','0','0','0','0','0','0','0','0','0',Temperature,'0','0' from #temp where Row='12'and PLevel=@y and Point=@z
--select * from #result
-- declare @avgtmpt varchar(20),@maxtmpt varchar(20)
select @avgtmpt=convert(varchar,round(avg(convert(float,Temperature)),2),20) from #temp where PLevel=@y and Point=@z
select @maxtmpt=convert(varchar,max(convert(float,Temperature)),20) from #temp where PLevel=@y and Point=@z insert #result2
select Surface,PLevel,sum(round(convert(float,a),2)) as a,sum(round(convert(float,b),2)) as b,sum(round(convert(float,c),2)) as c,sum(round(convert(float,d),2)) as d,sum(round(convert(float,e),2)) as e,sum(round(convert(float,f),2)) as f,sum(round(convert(float,g),2)) as g,sum(round(convert(float,h),2)) as h,sum(round(convert(float,i),2)) as i,sum(round(convert(float,j),2)) as j,sum(round(convert(float,k),2)) as k,sum(round(convert(float,l),2)) as l,@avgtmpt,@maxtmpt from #result
group by Surface,PLevel
delete #result set @y=@y+1
END
set @z=@z+1
END--select * from #result2
--select * from #temp update #result2 set a=a+'.0' where CHARINDEX('.',a)=0 and (len(rtrim(a))>0 and len(rtrim(a))<4) and isnumeric(a)=1
update #result2 set b=b+'.0' where CHARINDEX('.',b)=0 and (len(rtrim(b))>0 and len(rtrim(b))<4) and isnumeric(b)=1
update #result2 set c=c+'.0' where CHARINDEX('.',c)=0 and (len(rtrim(c))>0 and len(rtrim(c))<4) and isnumeric(c)=1
update #result2 set d=d+'.0' where CHARINDEX('.',d)=0 and (len(rtrim(d))>0 and len(rtrim(d))<4) and isnumeric(d)=1
update #result2 set e=e+'.0' where CHARINDEX('.',e)=0 and (len(rtrim(e))>0 and len(rtrim(e))<4) and isnumeric(e)=1
update #result2 set f=f+'.0' where CHARINDEX('.',f)=0 and (len(rtrim(f))>0 and len(rtrim(f))<4) and isnumeric(f)=1
update #result2 set g=g+'.0' where CHARINDEX('.',g)=0 and (len(rtrim(g))>0 and len(rtrim(g))<4) and isnumeric(g)=1
update #result2 set h=h+'.0' where CHARINDEX('.',h)=0 and (len(rtrim(h))>0 and len(rtrim(h))<4) and isnumeric(h)=1
update #result2 set i=i+'.0' where CHARINDEX('.',i)=0 and (len(rtrim(i))>0 and len(rtrim(i))<4) and isnumeric(i)=1
update #result2 set j=j+'.0' where CHARINDEX('.',j)=0 and (len(rtrim(j))>0 and len(rtrim(j))<4) and isnumeric(j)=1
update #result2 set k=k+'.0' where CHARINDEX('.',k)=0 and (len(rtrim(k))>0 and len(rtrim(k))<4) and isnumeric(k)=1
update #result2 set l=l+'.0' where CHARINDEX('.',l)=0 and (len(rtrim(l))>0 and len(rtrim(l))<4) and isnumeric(l)=1
update #result2 set avgtmpt=avgtmpt+'.0' where CHARINDEX('.',avgtmpt)=0 and (len(rtrim(avgtmpt))>0 and len(rtrim(avgtmpt))<4) and isnumeric(avgtmpt)=1
update #result2 set maxtmpt=maxtmpt+'.0' where CHARINDEX('.',maxtmpt)=0 and (len(rtrim(maxtmpt))>0 and len(rtrim(maxtmpt))<4) and isnumeric(maxtmpt)=1
select * from #result2 where a is not null end
else
begin select 0
endEndexec sp_Data_Select '','','P9A','',0,0
conn.Open(); string str = "sp_Data_Select"; SqlCommand cmd = new SqlCommand(str, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (chkBoxSelect.Checked)
{
dtb = "";
dte = "";
Row = "";
Level = "0";
Point = "0";
}
SqlParameter sp = new SqlParameter("@DtBegin", SqlDbType.VarChar, 20);
sp.Value = dtb;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@DtEnd", SqlDbType.VarChar, 20);
sp.Value = dte;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@Barn", SqlDbType.VarChar, 10);
sp.Value = BarnSel;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@Row", SqlDbType.VarChar, 5);
sp.Value = Row;
cmd.Parameters.Add(sp);
sp = new SqlParameter("@Level", SqlDbType.Int);
sp.Value = int.Parse(Level);
cmd.Parameters.Add(sp);
sp = new SqlParameter("@Point", SqlDbType.Int);
sp.Value = int.Parse(Point);
cmd.Parameters.Add(sp); //SqlDataReader sqlReader = cmd.ExecuteReader(); try
{
if (DataGridSelect.RowCount > 1)
{
foreach (DataGridViewRow rr in DataGridSelect.Rows)
{
DataGridSelect.Rows.Clear();
} SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
this.DataGridSelect.DataSource = ds.Tables[0];
for (int i = 0; i < DataGridSelect.Columns.Count; i++)
{ DataGridSelect.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
dataAdapter.Close();
哥哥,看题目啊……消息 217,级别 16,状态 1,过程 sp_Data_Select,第 569 行
超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
(
Barn varchar(10), --仓库编号
Row varchar(5), --列编号
PLevel int, --层编号
Point int, --点编号
Temperature varchar(10), --温度数值
Humidity varchar(10), --湿度数值
Dt varchar(20), --日期时间
)create table #temp
(
Barn varchar(10), --仓库编号
Row varchar(5), --列编号
PLevel int, --层编号
Point int, --点编号
Temperature varchar(10), --温度数值
Humidity varchar(10), --湿度数值
Dt varchar(20), --日期时间
)
set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str))
set @yy=convert(int,substring(@Str,1,charindex(',',@Str)-1))
set @Str=substring(@Str,charindex(',',@Str)+1,len(@Str)-charindex(',',@Str))
set @zz=convert(int,@Str)
PRINT @xx
PRINT @yy
--加上這一段看看賦值結果是否正確
不过如果如下规律成立的话,你可以改写下代码
insert #result
select @z,@y,
case when Row='01' then Temperature else '0' end,
case when Row='02' then Temperature else '0' end,
case when Row='03' then Temperature else '0' end,
case when Row='04' then Temperature else '0' end,
case when Row='05' then Temperature else '0' end,
case when Row='06' then Temperature else '0' end,
case when Row='07' then Temperature else '0' end,
case when Row='08' then Temperature else '0' end,
case when Row='09' then Temperature else '0' end,
case when Row='10' then Temperature else '0' end,
case when Row='11' then Temperature else '0' end,
case when Row='12' then Temperature else '0' end,
'0','0'
from #temp where PLevel=@y and Point=@z
set @xx=11
set @yy=5
set @zz=7
2.调用exec sp_Data_Select '','','P9A','',0,0 结果是啥
3.查看select Info from GrainSetting where Kind='Points' and Barn='P9A' 的结果
select Info 的结果是12,4,8
介个临时表在结束时怎么删啊,还要用表里的数据返回结果呢,但是返回结果的语句要放在最后一句吧……呵呵,我不太懂
其它看不出哪里有递归
Drop Table 临时表名
这都是以前看同事调程序养成的懒毛病,就在过程的END后面直接写调试语句,呵呵,本来以为在Begin End外面写东西是不会出问题的,谁知道这回折磨我够呛
恩,多谢了哥们,弄明白了,是我在Begin End 后面加了一句Exec 的原因,不过还是谢谢了,麻烦各位了,呵呵 现在正式结贴
select * from #temp 存在什麼數據