Cursor returning last row twice

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 2005 Forums

Transact-SQL (2005)

Cursor returning last row twice

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-12-31 : 18:34:12

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

885 Posts

Posted - 2013-01-01 : 09:47:39

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/

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-01-02 : 12:00:09

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources