Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3341
  • Last Modified:

CURSOR and FETCH STATUS

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
clangl
Asked:
clangl
  • 2
1 Solution
 
ShogunWadeCommented:
Yes..

YOU have no FETCH NEXT FROM .....   before the WHILE @@FETCH_STATUS   loop
0
 
ShogunWadeCommented:
The idomatic was of cursoring is :


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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now