2008年7月19日

Set Nocount

好吧,我只能說我對adodb生疏太久了,這樣的一個bug居然要快一個星期才找到...

事情是這樣的,我要做一份report,根據年月分,所以我寫了一段這樣的SQL


create procedure get_message_count_by_user
@ctrlId nvarchar(32),
@sdate datetime,
@edate datetime
as

declare @sd datetime, @ed datetime
create table #temp (sd datetime, ed datetime , ctTotal int, ctR int, ctS int, ctnp int)
insert #temp select sdate, edate, 0, 0, 0, 0 from sep_month(@sdate, @edate)
update #temp set ctTotal=t1
from (select sd, count(sid) t1 from #temp as s, messageboard
where contentdate>=s.sd and s.ed>contentdate and groupno=@ctrlId and deleteUser is null and
(status='2' or isnull(returnUser,'')<>@ctrlId )
group by sd ) as a
where #temp.sd=a.sd
update #temp set ctR=t1
from (select sd, count(sid) t1 from #temp as s, messageboard
where contentdate>=s.sd and contentdate<s.ed and groupno=@ctrlId and deleteUser is null and
status='2'
group by sd ) as a
where #temp.sd=a.sd
update #temp set ctS=t1
from (select sd, count(sid) t1 from #temp as s, messageboard
where contentdate>=s.sd and contentdate<s.ed and groupno=@ctrlId and deleteUser is null and
status='5'
group by sd ) as a
where #temp.sd=a.sd
update #temp set ctNP=ctTotal-ctR-ctS

select * from #temp


呼叫這個stored procedure,在asp.net一點問題也沒有,但是在asp中,我就是沒有辦法叫adodb取得#temp中的資料。測試了很久,甚至把stored procedure一行一行拆開來測試,結果發現:Insert #temp select 這一行一加入之後,後面的select敘述就沒有辦法傳回值了。這使得我想起很久很久以前(應該是七年前)我也遇過一個類似的問題,於是把資料庫一個一個找出來,在眾多的stored procedure中搜尋,終於看到一個熟悉的字眼:set nocount。於是在開頭(第六行)跟select * from #temp之前(第29行)分別加上:set nocount on, set nocount off,問題就解決了。

不過說到set nocount,這讓我想起許多陳年往事,還牽涉到一位如今是M$名嘴級的講師。這是個又臭又長的故事,有機會再說。