SQLServer存储过程

USE [GPSClient]
GO
/****** 对象: ?StoredProcedure [dbo].[myProc] ? ?脚本日期: 12/25/2015 14:56:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<span style="white-space:pre">		</span><Author,Name>
-- Create date: <Create Date,>
-- Description:<span style="white-space:pre">	</span><Description,>
-- =============================================
ALTER PROCEDURE [dbo].[StatisticsContruction]
-- Add the parameters for the stored procedure here
@CustomID varchar(10)
AS
BEGIN
? ? ?--循环接收变量:设备编号,工地,设备状态
? ? ?declare @ClientSerial varchar(10),@Construction varchar(20),@ClientStatus bit
? ? ?declare @NoDefineOnline int,@NoDefineOffline int --未定义工地砂浆罐统计
? ? ?set @NoDefineOnline ? = 0
? ? ?set @NoDefineOffline ?= 0


? ? ?declare @tempOnline int,@tempOffline int --在线和离线统计变量
? ? ?set @tempOnline ?= 0
? ? ?set @tempOffline = 0


     SET NOCOUNT ON;
? ? ?--定义游标,指向由server_synchro_info和##realtimedata合并的表
? ? ?Declare curStudentFee Cursor for?
     SELECT server_synchro_info.clientserial,server_synchro_info.Construction,##realtimedata.clientstutas
? ? ? ?FROM ##realtimedata,server_synchro_info
? ? ? ?WHERE ##realtimedata.clientserial = server_synchro_info.clientserial?
? ? ? ?and server_synchro_info.CustomID = @CustomID


? ? ?--打开游标 ?
? ? ?Open curStudentFee
? ? ?--创建临时表，存储工地名称
? ? ?select distinct(Construction) into #tempAC from server_synchro_info where CustomID=@CustomID
? ? ?alter table #tempAC add OnLineCount ? int not null default 0
? ? ?alter table #tempAC add OffLineCount ?int not null default 0
? ? ?insert into #tempAC(Construction)values('NoDefine')




? ? ?Fetch Next From curStudentFee Into @ClientSerial,@Construction,@ClientStatus--取第一条记录存入三个变量：设备号,工地名,是否在线?
? ? ?While ( @@Fetch_Status = 0 ) ? ??
? ? ?begin ?
? ? ? ? --print ''+rtrim(@ClientSerial)+','+rtrim(@Custruction)+','+cast(@clientStatus as varchar(1))+''+',';---处理结果
? ? ? ? if @clientStatus = 1 --在线
? ? ? ? ? begin
? ? ? ? ? ? select @tempOnline=OnLineCount ?from #tempAC where Construction = @Construction
? ? ? ? ? ? set @tempOnline = @tempOnline + 1
? ? ? ? ? ??
? ? ? ? ? ? update #tempAC set OnlineCount=@tempOnline where Construction = @Construction
? ? ? ? ? end
? ? ? ? else --离线
? ? ? ? ? begin
? ? ? ? ? ? select @tempOffline=OffLineCount from #tempAC where Construction = @Construction
? ? ? ? ? ? set @tempOffline = @tempOffline +1
? ? ? ? ? ? update #tempAC set OfflineCount=@tempOffline where Construction = @Construction
? ? ? ? ? end


? ? ? ? Fetch Next From curStudentFee into @ClientSerial,@ClientStatus----下一条 ?
? ? ?end ??


? ? ?--关闭游标 ? ??
? ? ?Close curStudentFee ?
? ? ?--释放游标 ?
? ? ?Deallocate curStudentFee ??


? ? ?--未指定工地的砂浆罐统计
? ? ?select @tempOnline = sum(OnlineCount),@tempOffline = sum(OfflineCount) from #tempAC
? ? ?select @NodefineOnline = count(clientserial) from ##realtimedata ?where ClientStutas = 1 and defaultTag2 =@CustomID
? ? ?select @NodefineOffline = count(clientserial) from ##realtimedata where ClientStutas = 0 and defaultTag2 =@CustomID
? ? ?update #tempAC set OnlineCount = @NoDefineOnline-@tempOnline,OfflineCount = @NoDefineOffline-@tempOffline where Construction ='NoDefine' ? ??


? ? ?select * from #tempAC
END

declare @CustomID varchar(10)
set @CustomID = '200023'
exec StatisticsContruction @CustomID

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!