clangl
asked on
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
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?
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FETCH NEXT FROM ........
WHERE @@FETCH_STATUS=0
BEGIN
....
....
....
FETCH NEXT FROM..........
END