Posted by Erland Sommarskog on 01/30/07 11:28

Daniel.Peaper@gmail.com (Daniel.Peaper@gmail.com) writes:
> Will this not just process the first record CustID=1? How would I go
> about processing the whole table? Do I have to build a client
> application to process a loop or can I proccess this on the SQL
> server?

You could set up a cursor. However, using loops is not a very efficient
use of SQL Server. It may be better to replace the stored procedure
with statements that operates on the entire table at once. Since the
stored procedure is another server, this is not exactly trivial, depending
a bit of what's in that remote procedure.

The way to write a cursor would be:

DECLARE custcur INSENSITIVE CURSOR FOR
SELECT CustID, CustName, CustContact, CustPhone
FROM Customers

OPEN custcur

WHILE 1 = 1
BEGIN
FETCH custcur INTO @CustID, @CustName, @CustContact, @CustPhone
IF @@fetch_status <> 0
BREAK

EXEC SERVER.db.dbo.remote_sp @CustID, @CustName, @CustContact, @CustPhone
END

DEALLOCATE custcur


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]