SQLServer存储过程

两张表合并查询，并以其中一张表的字段为条件统计

USE [GPSClient]
GO
/****** 对象:  StoredProcedure [dbo].[myProc]    脚本日期: 12/25/2015 14:56:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[StatisticsContruction]
-- Add the parameters for the stored procedure here
    @CustomID varchar(10),
    @recCount int=0 output
    
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--取第一条记录存入@result中  
     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,@Construction,@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
     
     set @Reccount = @@ROWCOUNT 


END

SQLServer管理器中，测试方法:

declare @CustomID varchar(10)--输入参数
declare @recCount int  --输出参数
set @CustomID = '200023'
exec StatisticsContruction @CustomID,@recCount output
统计某客户的每个工地上的在线和离线的设备的数量

在ASP服务器中调用如下：

<%@ Language=VBScript %>
<%	
	  dim i,RecSize
	  Dim rs 
	  i = 0 :recSize = 0

	  
	  '建一个command对象 
	  set CmdSP = Server.CreateObject("ADODB.Command")
      '建立连结 
      CmdSP.ActiveConnection = "Driver={SQL Server};server=218.90.205.66;Uid=zyuanli;Pwd=123456;Database=GPSClient"  	  

	  '定义command 对象调用名称  
      CmdSP.CommandText = "StatisticsContruction" 
	  
      '设置command调用类型是存储过程 (adCmdSPStoredProc = 4) 
      CmdSP.CommandType = 4 'adCmdSPStoredProc 
      CmdSP.Prepared = true '要求将SQL命令先行编译
	  
      CmdSP.Parameters.Append CmdSP.CreateParameter("@CustomID", 200, 1, 10, "200023")
	  CmdSP.Parameters.Append CmdSP.CreateParameter("@recCount", 2, 4) 
	 
		Set rs = CmdSP.Execute 
		rs.close

		RecSize = CmdSP("@recCount")'读参数


		rs.open
		response.write "["
		
		do while not rs.EOF
		   i = i+1
		
		response.write "{" & """ConstructionName"":"""&  trim(rs.Fields(0).value) & ""&"""," _
		&"""OnLine"":"""& trim(rs.Fields(1).value)&"""," _
		&"""OffLine"":"""& trim(rs.Fields(2).value)&"""" 
		
		if i < RecSize then
		  response.write "},"
		else
		  response.write "}"
		end if
		
		response.write "<br>"
		
		
		rs.MoveNext 
		loop

		response.write "]"
		
		
		Set rs = nothing 
		Set CmdSP.ActiveConnection = nothing 
		Set CmdSP = nothing 
%>