Solved

CURSOR and FETCH STATUS

Posted on 2003-12-09
2
3,321 Views
Last Modified: 2009-02-06
I have the following SQL Statement against MS SQL Server:
DECLARE @acct char(10)
SELECT @acct='418895'
DECLARE cur_comor CURSOR LOCAL SCROLL FOR SELECT TOP 10 [24] FROM tblComor WHERE PatAcctPA=@acct ORDER BY [24TeirStatus]
DECLARE @sql varchar(8000)
DECLARE @sql_val varchar(8000)
DECLARE @c char(10)
DECLARE @i as int
OPEN cur_comor
--print @@FETCH_STATUS
SELECT @i=1
SELECT @sql='insert into #piv_comor(PatacctPA'
SELECT @sql_val = 'VALUES(' + @acct
WHILE @@FETCH_STATUS=0
      BEGIN
      FETCH NEXT FROM cur_comor into @c
      --print 'HELLO: ' + @c
      SELECT @sql=@sql+',comor'+CONVERT(varchar(2),@i)
      SELECT @i=@i+1
      SELECT @sql_val=@sql_val+','''+@c +''''
      end
close cur_comor
--PRINT @sql + ') ' + @sql_val + ')'
DEALLOCATE cur_comor

If I run it once it is fine.  If I try to run it a second time I get a FetchStatus of -1.
Does anyone know what is going on and what I am doing wrong?
0
Comment
Question by:clangl
  • 2
2 Comments
 
LVL 18

Accepted Solution

by:
ShogunWade earned 250 total points
ID: 9904614
Yes..

YOU have no FETCH NEXT FROM .....   before the WHILE @@FETCH_STATUS   loop
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904625
The idomatic was of cursoring is :


FETCH NEXT FROM ........
WHERE @@FETCH_STATUS=0
BEGIN
....
....
....
FETCH NEXT FROM..........
END
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now