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
Cursor returning last row twice
Posted - 12/31/2012 : 18:34:12
