Purpose of FETCH NEXT FROM before WHILE loop? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Purpose of FETCH NEXT FROM before WHILE loop?

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-13 : 22:34:03

Please answer both questions. In the following code:

1. What is the purpose of the FETCH NEXT FROM INTO statement that appears before entering the WHILE loop? Why would you need to do this if this already appears inside of the WHILE loop anyway?

2. If the answer to #1 is to already have a value in CustID before you enter the loop, then I would ask, if you can simply omit the first FETCH NEXT statement and take the FETCH NEXT statement in the loop and put that on the line before the PRINT statement, then PRINT the value in CustID. Wouldn't that also work?

declare @CustId nchar(5)
declare @RowNum int
declare CustList cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @CustId
FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-14 : 00:16:11

[code]
declare @CustId nchar(5)
declare @RowNum int

declare CustList cursor for
select top 5 CustomerID from Northwind.dbo.Customers

OPEN CustList

FETCH NEXT FROM CustList --if without this 2 rows, what will yours @CustID shows for the first print?
INTO @CustId --

set @RowNum = 0

WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1

print cast(@RowNum as char(1)) + ' ' + @CustId

FETCH NEXT FROM CustList

INTO @CustId
END

CLOSE CustList
DEALLOCATE CustList
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-14 : 00:20:24

see this as well.
http://msdn.microsoft.com/en-us/library/aa226062(v=sql.80).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:20:40

1. The first fetch is required because the condition that you're on WHILE loop is @@FETCH_STATUS = 0 ie whether cursor returns any record. For this to work, you should have fetched a record from cursor.
2. You can dispense with the fetch next outside loop but in that case you need to change WHILE loop condition logic based on may be count of records processed or something for which you need to retrieve count of records beforehand

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-14 : 08:38:09

A better question to ask is....

Why use a cursor........ ever

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-14 : 09:53:18

There ARE valid reasons. Just not a lot of them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources