SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Purpose of FETCH NEXT FROM before WHILE loop?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/13/2013 :  22:34:03  Show Profile  Reply with Quote
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

Malaysia
964 Posts

Posted - 05/14/2013 :  00:16:11  Show Profile  Reply with Quote

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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 05/14/2013 :  00:20:24  Show Profile  Reply with Quote
see this as well.
http://msdn.microsoft.com/en-us/library/aa226062(v=sql.80).aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  00:20:40  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/14/2013 :  08:38:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/14/2013 :  09:53:18  Show Profile  Visit russell's Homepage  Reply with Quote
There ARE valid reasons. Just not a lot of them.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000