January 22, 2010 at 9:05 am
I know this is weird, but I don't think I did anything wrong, it just suddently failed.
original code is using cursor in sp,
here is the code:
declare c cursor for
select DocID = doc.ID,
OrgUnitID,
title = case when doc.title <> '' then doc.title else '' end,
ref = case when doc.ref <> '' then doc.ref else RefYear + '-' + Refno end,
FileName = case when doc.FileName <> '' then doc.FileName else '' end,
FileExt = case when doc.FileExt <> '' then doc.FileExt else '' end,
doctype = case when doc.doctype != 0 then dt.name else 'Unkwown' end,
DateFirstIssued = doc.DateFirstIssued,
lastreviewdate = doc.lastreviewdate,
nextreviewdate = doc.nextreviewdate
from infosys i
inner join document doc on doc.sysid = i.sysid
left outer join doctype dt on dt.id = doc.doctype
where
doc.docStatus like case when @Status <> 'All' then @Status else '%%' end
and i.[Type] like case when @Type <> 'All' then @Type else '%%' end
OPEN c
FETCH NEXT FROM c INTO @DocID, @OrgUnitID, @Title, @Ref, @FileName, @FileExt, @NewDocType, @DateFirstIssued, @LastReviewDate, @NextReviewDate
WHILE @@FETCH_STATUS = 0
BEGIN
.......
when I noticed it is not working this morning, I first thought maybe something wrong with the cursor, but really it WAS working before, why it failed now? I double checked and found at the very beginning @@FETCH_STATUS = -1. which means nothing was inserted into the cursor, but I have no problem seeing those records if not inserting them into cursor.
Anyway, then I changed my code into using temp table, code is as below:
insert into #t1
select DocID = doc.ID,
OrgUnitID,
title = case when doc.title <> '' then doc.title else '' end,
ref = case when doc.ref <> '' then doc.ref else RefYear + '-' + Refno end,
FileName = case when doc.FileName <> '' then doc.FileName else '' end,
FileExt = case when doc.FileExt <> '' then doc.FileExt else '' end,
doctype = case when doc.doctype != 0 then dt.name else 'Unkwown' end,
DateFirstIssued = doc.DateFirstIssued,
lastreviewdate = doc.lastreviewdate,
nextreviewdate = doc.nextreviewdate
from infosys i
inner join document doc on doc.sysid = i.sysid
left outer join doctype dt on dt.id = doc.doctype
where
i.Status like case when @Status <> 'All' then @Status else '%%' end
and i.[Type] like case when @Type <> 'All' then @Type else '%%' end
SET @TabCount = @@ROWCOUNT
select * from #t1
Weird thing happens again, although there are over 1000 records in the select result, but the last statement (select * from #t1 --- which I use it to double check) won't return any result.
What could be wrong with my database? or server?
thanks.
Any hint is appreciated.
January 22, 2010 at 9:15 am
First thing that I would do is stop using a Cursor to perform a simple Insert of data into a table.
SQL Server is a set based database, if you want to use cursors, change your database to Oracle.
You should never, ever use a Cursor to perform a data insert.
Andrew SQLDBA
January 22, 2010 at 9:31 am
Cursor is always considered as an evil 😀
But anyway my problem isn't cursor's problem, because as you can see, temp table is not working either. I suspect something wrong with the server/database itself.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply