FETCH NEXT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FETCH NEXT

Author

Topic

Kanati
Starting Member

36 Posts

Posted - 2004-07-07 : 13:04:09

In a cursor (yeah, yeah, I know. :) ) can I do something like:


FETCH NEXT FROM BLAHCURS INTO @DATA

WHILE @@FETCH_STATUS = 0
BEGIN
  IF LEFT(@DATA,1) = 'T'
  BEGIN
    --Skippping a record...
    FETCH NEXT FROM BLAHCURS INTO @DATA
    FETCH NEXT FROM BLAHCURS INTO @DATA
    PRINT @DATA
    RETURN 1
  END
  FETCH NEXT FROM BLAHCURS INTO @DATA
END

It SEEMS like it doesn't actually fetch the next record until it checks @@FETCH_STATUS in the while statement. Is this true?

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 13:46:34

I believe it's evaluated at the end of the block, so your code could cause an error if you read past the end of the cursor results...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 13:55:54

Yeah..it's evaluated at the end, but it doesn't throw an error...

Another feature!


USE Northwind

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
GO

DECLARE myCursor99 CURSOR FOR SELECT Col1 FROM myTable99
DECLARE @Col1 int

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @Col1
SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
SELECT @Col1

WHILE @@FETCH_STATUS = 0
  BEGIN
	FETCH NEXT FROM myCursor99 INTO @Col1
	SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
	SELECT @Col1
	FETCH NEXT FROM myCursor99 INTO @Col1
	SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
	SELECT @Col1
  END
CLOSE myCursor99
DEALLOCATE myCursor99
GO

DROP Table myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources