我有一存储过程,里面有一系列的统计SQL语句,最后一句是select * from 表(临时表),在查询分析器中有结果,但在VB中用ADO的RecordSet对象打开该存储过程后,不能取结果,显示RecordSet对象关闭,这是怎么回事?用NextRecordet方法也不行,不知各位遇到这样的问题没有?
...全文
999打赏收藏
存储过程问题?
我有一存储过程,里面有一系列的统计SQL语句,最后一句是select * from 表(临时表),在查询分析器中有结果,但在VB中用ADO的RecordSet对象打开该存储过程后,不能取结果,显示RecordSet对象关闭,这是怎么回事?用NextRecordet方法也不行,不知各位遇到这样的问题没有?
IF @TimeFlag='d'
BEGIN
SET @SQL1='SELECT 时间=CAST(passedtime as tinyint)'
SET @SQL3='GROUP BY passedtime ORDER BY passedtime'
SET @SQL4='SELECT 时间=DATEPART(hh,passedtime)'
SET @SQL5='GROUP BY DATEPART(hh,passedtime),cartype,cardstatus'
SET @i=0
WHILE(@i<=23)
BEGIN
INSERT INTO #FluxStat VALUES(@i,0,0,0,0,0,0,0,0)
SET @i=@i+1
END
END
ELSE IF @TimeFlag='m'
BEGIN
SET @SQL1='SELECT 时间=DATEPART(dd,passeddate)'
SET @SQL3='GROUP BY DATEPART(dd,passeddate) ORDER BY DATEPART(dd,passeddate)'
SET @SQL4='SELECT 时间=DATEPART(dd,passedtime)'
SET @SQL5='GROUP BY DATEPART(dd,passedtime),cartype,cardstatus'
--计算月份的最大天数
SET @i=1
SET @MaxDay=DATEPART(dd,@EndDatetime)
WHILE(@i<=DATEDIFF(mm,@StartDatetime,@EndDatetime))
BEGIN
SET @TempDate=DATEADD(mm,@i+1,@StartDatetime)
SET @j=DATEPART(dd,DATEADD(dd,-1,CONVERT(datetime,DATENAME(yy,@TempDate)+'-'+DATENAME(mm,@TempDate)+'-01')))
IF @j>@MaxDay
SET @MaxDay=@j
IF @j=31
BREAK
SET @i=@i+1
END
SET @i=1
WHILE(@i<=@MaxDay)
BEGIN
INSERT INTO #FluxStat VALUES(@i,0,0,0,0,0,0,0,0)
SET @i=@i+1
END
END
ELSE
BEGIN
SET @SQL1='SELECT 时间=DATEPART(mm,passeddate)'
SET @SQL3='GROUP BY DATEPART(mm,passeddate) ORDER BY DATEPART(mm,passeddate)'
SET @SQL4='SELECT 时间=DATEPART(mm,passedtime)'
SET @SQL5='GROUP BY DATEPART(mm,passedtime),cartype,cardstatus'
SET @i=1
WHILE(@i<=12)
BEGIN
INSERT INTO #FluxStat VALUES(@i,0,0,0,0,0,0,0,0)
SET @i=@i+1
END
END
IF @tollcenterno='所有' and @tollgateno='所有'
SET @SQL2=''
ELSE IF @tollcenterno<>'所有' and @tollgateno='所有'
SET @SQL2='AND tollcenterno=''' + @tollcenterno + ''''
ELSE
SET @SQL2='AND tollcenterno=''' + @TollCenterNo + ''' AND tollgateno=''' + @TollgateNo + ''''
IF DATEPART(mi,@StartDatetime)<>0 OR DATEPART(ss,@StartDatetime)<>0
BEGIN
SET @Datetime1=CONVERT(char(10),@StartDatetime,102)+' '+DATENAME(hh,@StartDatetime)+':59:59'
SET @Datetime2=DATEADD(ss,1,@Datetime1)
SET @Datetime4=CONVERT(char(10),@EndDatetime,102)+' ' +DATENAME(hh,@EndDatetime)+':00:00'
SET @Datetime3=DATEADD(ss,-1,@Datetime4)
SET @SQL='INSERT INTO #FluxStat2 '+@SQL4+',车型=cartype,卡状态=cardstatus,流量=count(cartype)
FROM passedcars WHERE (passedtime BETWEEN ''' + CONVERT(varchar(30),@StartDatetime,120)+ ''' AND ''' +
CONVERT(varchar(30),@Datetime1,120)+ ''' OR passedtime BETWEEN ''' + CONVERT(varchar(30),@Datetime4,120)+
''' AND ''' + CONVERT(varchar(30),@EndDatetime,120)+''') '+ @SQL2 + ' ' +@SQL5
EXECUTE(@SQL)
INSERT INTO #FluxStat1 SELECT 时间,SUM(CASE WHEN 车型='01' and (卡状态='02' or 卡状态='04' or 卡状态='05' or 卡状态='10')
THEN 流量 ELSE 0 END),SUM(CASE WHEN 车型='02' and (卡状态='02' or 卡状态='04' or 卡状态='05' or 卡状态='10')
THEN 流量 ELSE 0 END),SUM(CASE WHEN 车型='03' and (卡状态='02' or 卡状态='04' or 卡状态='05' or 卡状态='10')
THEN 流量 ELSE 0 END),SUM(CASE WHEN 车型='04' and (卡状态='02' or 卡状态='04' or 卡状态='05' or 卡状态='10')
THEN 流量 ELSE 0 END),SUM(CASE WHEN 车型='05' and (卡状态='02' or 卡状态='04' or 卡状态='05' or 卡状态='10')
THEN 流量 ELSE 0 END),SUM(CASE WHEN 卡状态='07' THEN 流量 ELSE 0 END),
SUM(CASE WHEN 卡状态='09' THEN 流量 ELSE 0 END),SUM(CASE WHEN (卡状态='01' or 卡状态='03' or 卡状态='06' or 卡状态='08')
THEN 流量 ELSE 0 END)
FROM #FluxStat2 GROUP BY 时间
UPDATE A
SET A.一类车=A.一类车+B.一类车,A.二类车=A.二类车+B.二类车,A.三类车=A.三类车+B.三类车,
A.四类车=A.四类车+B.四类车,A.五类车=A.五类车+B.五类车,A.免费=A.免费+B.免费,
A.月卡=A.月卡+B.月卡,A.违章=A.违章+B.违章
FROM #FluxStat A,#FluxStat1 B
WHERE A.时间=B.时间
TRUNCATE TABLE #FluxStat1
END
ELSE
BEGIN
SET @Datetime2=@StartDatetime
SET @Datetime3=@EndDatetime
END
SET @SQL='INSERT INTO #FluxStat1 '+@SQL1+',一类车=SUM(flux1),二类车=SUM(flux2),三类车=SUM(flux3)
,四类车=SUM(flux4),五类车=SUM(flux5),免费=SUM(free),月卡=SUM(monthcard)
,违章=SUM(invalidation) FROM daysreport WHERE CONVERT(char(10),passeddate,102)+passedtime>=''' +
CONVERT(char(10),@Datetime2,102)+datename(hh,@Datetime2) +'''' +
' AND CONVERT(char(10),passeddate,102)+passedtime<='''+CONVERT(char(10),@Datetime3,102)+
datename(hh,@Datetime3)+''' '+ @SQL2 + ' ' +@SQL3
EXECUTE(@SQL)
UPDATE A
SET A.一类车=A.一类车+B.一类车,A.二类车=A.二类车+B.二类车,A.三类车=A.三类车+B.三类车,
A.四类车=A.四类车+B.四类车,A.五类车=A.五类车+B.五类车,A.免费=A.免费+B.免费,
A.月卡=A.月卡+B.月卡,A.违章=A.违章+B.违章
FROM #FluxStat A,#FluxStat1 B
WHERE A.时间=B.时间
SELECT 时间,一类车,二类车,三类车,四类车,五类车,免费,月卡,违章,
合计=一类车+二类车+三类车+四类车+五类车+免费+月卡+违章
FROM #FluxStat
GO