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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Cursor returning last row twice
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GregDDDD
Posting Yak Master

120 Posts

Posted - 12/31/2012 :  18:34:12  Show Profile  Reply with Quote
I have a program written in Visual Studio which goes out nightly and polls a web service for claims data. I pull a list of clients from a SQL Server table and then call the web service once for each client. The response from the service gives me a 'CLIENT NOT FOUND' when the service can't identify them on first, last and DOB. When I get the CLIENT NOT FOUND message I write the client info to a table and then have SQL Server send out and email from an sp later that night.

I use a cursor (bad, I know) to get the names and put them in the body of the email. The WHILE loop always reads the last row twice. It seems like it reads the last row, gets to the top of the loop, and then hits it again as it exit the loop. Is there a way around this.


	declare @tbl_people TABLE (
	[MemberID] varchar(30) NOT NULL,
	[LastName] varchar(30) NOT NULL,
	[FirstName] varchar(30) NOT NULL,
	[DOB] datetime
	)


	insert into @tbl_people
	values
	  ('1', 'doe', 'john', '7/1/2011')

	insert into @tbl_people
	values
	  ('2', 'doe', 'jane', '1/1/1980')

	DECLARE @ClientList varchar(max)
	DECLARE @Counter int

	DECLARE @OneClient VARCHAR(300);

    DECLARE crs CURSOR READ_ONLY 
	FOR
	SELECT [MemberID] + ' ' + [LastName] + ' ' + [FirstName] + ' ' + CONVERT(VARCHAR(10), [DOB], 101) AS Client FROM @tbl_people
	
	OPEN crs 
	
	FETCH NEXT FROM crs INTO @OneClient 
	
	SET @ClientList = @OneClient  + CHAR(13) + CHAR(10)
	set @Counter = LEN(@OneClient) - 2

	WHILE @@FETCH_STATUS <> -1
	BEGIN  
		FETCH NEXT FROM crs  
		INTO @OneClient		
		SET @ClientList = @ClientList + @OneClient + CHAR(13) + CHAR(10)
		SET @Counter = @Counter + 1
	END
	CLOSE crs
	DEALLOCATE crs
	
	IF @Counter > 0 BEGIN
		print @ClientList
	END
	


Greg

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 01/01/2013 :  09:47:39  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
declare @tbl_people TABLE (
[MemberID] varchar(30) NOT NULL,
[LastName] varchar(30) NOT NULL,
[FirstName] varchar(30) NOT NULL,
[DOB] datetime
)


insert into @tbl_people
values
('1', 'doe', 'john', '7/1/2011')

insert into @tbl_people
values
('2', 'doe', 'jane', '1/1/1980')

DECLARE @ClientList varchar(max)
DECLARE @Counter int

DECLARE @OneClient VARCHAR(300);

DECLARE crs CURSOR READ_ONLY
FOR
SELECT [MemberID] + ' ' + [LastName] + ' ' + [FirstName] + ' ' + CONVERT(VARCHAR(10), [DOB], 101) AS Client FROM @tbl_people

OPEN crs

FETCH NEXT FROM crs INTO @OneClient

SET @ClientList = @OneClient + CHAR(13) + CHAR(10)
set @Counter = LEN(@OneClient) - 2

WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH NEXT FROM crs INTO @OneClient
SET @ClientList = @ClientList + @OneClient + CHAR(13) + CHAR(10)
SET @Counter = @Counter + 1
FETCH NEXT FROM crs INTO @OneClient
END

CLOSE crs
DEALLOCATE crs

IF @Counter > 0 BEGIN
print @ClientList
END


--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 01/02/2013 :  12:00:09  Show Profile  Reply with Quote
Thanks. That seems counter-intuitive to have that FETCH twice in the loop, but it works, so I won't waste time worrying about it.

Greg
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.06 seconds. Powered By: Snitz Forums 2000