sqlserver存储过程进行产量查询

-- ================================================
-- 单日内24小时产量统计
-- ================================================
if exists(select * from sysobjects where name='ups_QueryDataOfSingleDay')
drop procedure ups_QueryDataOfSingleDay  --删除已创建的存储过程
go

CREATE PROCEDURE ups_QueryDataOfSingleDay 
@QueryDatetime  datetime,
@StationID  varchar(10),
@Remain  integer  output
AS
BEGIN
	-- 不返回受影响的行数
	SET NOCOUNT ON
	--创建临时表
	create table  #tWeight
	(
	    id integer,
		TonWeight integer,
		DisWeight integer
	)

	declare @i integer,@StartDate datetime,@TonTotalWeight integer,@DisTotalWeight integer
    set @i = 0
	set @StartDate = Convert(varchar(10),@QueryDatetime,120)
	set @StartDate = Convert(varchar(19),@StartDate+'00:00:00',120)
	while @i < 24
	begin
	   --投料
       select @TonTotalWeight = IsNull(sum(DiffWeight),0) from t_SaveRecordData where 
	              (ThisTime between @StartDate and DATEADD(hour,1,@StartDate)) and DiffWeight > 0
	   --排料
       select @DisTotalWeight = IsNull(sum(DiffWeight),0) from t_SaveRecordData where 
	              (ThisTime between @StartDate and DATEADD(hour,1,@StartDate)) and DiffWeight < 0
       --存入临时表
	   insert into #tWeight(id,TonWegiht, DisWeight)values(@i, @TonTotalWeight,@DisTotalWeight)

	   set @StartDate = DATEADD(hour,1,@StartDate)--时间更新
	   set @i = @i +1
    end
	select  * from #tWeight
	--删除临时表
	drop table #tWeight
    --统计余量
	select  @Remain = isnull(sum(convert(int,Gweight)),0) from ##Gpsrealtimedata,TankInfo where 
    (TankInfo.GPSID = ##GpsrealtimeData.GpsID) 
	and TankInfo.StationID = @StationID and CONVERT(int,Gweight) >= 0
END
GO

在查询管理工具中:

​​​​​​​declare  @a integer 
exec ups_QueryDataOfSingleDay '2019-05-08','1001',@a output
select @a

在C++Builder中调用,代码如下:

AnsiString strSql = "exec ups_QueryDataOfSingleDay :qtime,:sid,:Remain output";

m_pAdo->Active = false;
m_pAdo->SQL->Clear();
m_pAdo->SQL->Add(strSql);
m_pAdo->Parameters->Items[0]->Value = dt;
m_pAdo->Parameters->Items[1]->Value = szStationID;
m_pAdo->Parameters->Items[2]->Value = 0;//输出参数须赋值
m_pAdo->Active = true;

STWeight24Hours  wh={0};
int i = 0;
while(!m_pAdo->Eof)
{
    wh.nTonWeight[i]  = m_pAdo->FieldByName("TonWeight")->AsInteger;//临时表字段
    wh.nDisWeight[i]  = m_pAdo->FieldByName("DisWeight")->AsInteger;
    i++;
    m_pAdo->Next();
}
wh.nTonWeight[24] = wh.nTonWeight[0];
wh.nDisWeight[24] = wh.nDisWeight[0];
wh.nRemainWeight = m_pAdo->Parameters->ParamByName("Remain")->Value;//读输出参数