In my sp, i create several cursor, Now the sP go some errors, and I need to
drop the cursor,
Any command can let drop all the cursor ??
thx

--
..

Drop table? Drop cursor? Which one? For the cursor, just declare it as a
local cursor:

DECLARE cursorname CURSOR LOCAL

And you don't have to worry too much with this sort of thing. The scope
will be the batch or proc and it will go away. Otherwise you need to close
and deallocate it in your error handling, or if you cannot reach the error
handling code, kill the connection before rerunning.


With that said, the best plan is to never use cursors. Post what you are
trying to do and someone will tell you a better, non cursor way.

--
----------------------------------------------------------------------------
Louis Davidson - drsql (AT) hotmail (DOT) com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored

"Agnes" <agnes (AT) dynamictech (DOT) com.hk> wrote

Thanks Louis, Here the following is my code , ( i need to select the 1st
table , put them into cursor and then insert into another table), but I find
there is an another error (Time out expired)

select Rvd.docno,Rvd.cocode,sum(Rvd.fdebit-rvd.fcredit) as paidinvamt
,sum(Rvd.debit-rvd.credit) as paidbaseamt
into CursorSettleTable_rv
from DTS_Account.dbo.rvdetail rvd, DTS_Account.dbo.rvheader RvH
where rvd.voucherno = rvh.voucherno and rvh.validsw = 1 and rvh.transdate <=
@settledate and rvd.cocode in (select cocode from
DTS_Master.dbo.tmp_acctcompany where reportid = @reportid)
group by rvd.docno ,rvd.cocode


select Pvd.docno,Pvd.cocode,sum(Pvd.fdebit-pvd.fcredit) as paidinvamt
,sum(Pvd.debit-pvd.credit) as paidbaseamt
into CursorSettleTable_pv
from DTS_Account.dbo.Pvdetail Pvd, DTS_Account.dbo.Pvheader PvH
where Pvd.voucherno = Pvh.voucherno and Pvh.validsw = 1 and Pvh.transdate <=
@settledate and pvd.cocode in (select cocode from
DTS_Master.dbo.tmp_acctcompany where reportid = @reportid)
group by pvd.docno ,pvd.cocode

insert into DTS_Account.dbo.tmp_csrvpv
(docno,cocode,paidinvamt,paidbaseamt,reportid)
select docno,cocode,paidinvamt,paidbaseamt,@reportid as reportid from
CursorSettleTable_rv

insert into DTS_Account.dbo.tmp_csrvpv
(docno,cocode,paidinvamt,paidbaseamt,reportid)
select docno,cocode,paidinvamt,paidbaseamt,@reportid as reportid from
CursorSettleTable_pv



drop table CursorSettleTable_rv
drop table CursorSettleTable_pv

Well, I think you could probably code this without a cursor, or at least
with a more reasonable cursor, but the quick fix for now is to use temporary
tables.

,sum(Rvd.debit-rvd.credit) as paidbaseamt
into #CursorSettleTable_rv
from DTS_Account.dbo.rvdetail rvd, DTS_Account.dbo.rvheader RvH

So now the table will only live for the current connection, or if it is in a
stored procedure, for the stored procedure. If it is a batch, you might
want to include code like:

if object_id('tempdb..#CursorSettleTable_rv') is not null
begin
drop table #CursorSettleTable_rv
end

Just to be sure the table is not there.

--
----------------------------------------------------------------------------
Louis Davidson - drsql (AT) hotmail (DOT) com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored

"Agnes" <agnes (AT) dynamictech (DOT) com.hk> wrote