SQLServer存储过程

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

USE [GPSClient]GO/****** 对象:  StoredProcedure [dbo].[myProc]    脚本日期: 12/25/2015 14:56:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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    ASBEGIN     --循环接收变量:设备编号,工地,设备状态     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.closeRecSize = CmdSP("@recCount")'读参数rs.openresponse.write "["do while not rs.EOF   i = i+1response.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 ifresponse.write "<br>"rs.MoveNext loopresponse.write "]"Set rs = nothing Set CmdSP.ActiveConnection = nothing Set CmdSP = nothing %>