Hi All,

I am running SQL2000. Can anyone tell me how I can use the contents of
a table as parameters for a stored procedure that resides on a
different SQL server?

I have a table:

Customers:
Cust_ID
Cust_Name
Cust_Contact
Cust_Phone

I need to execute a stored procedure and pass each of the above as
parameters:

@CustID, @CustName, @CustContact,@CustPhone

The stored procedure also returns an error code.

Thanks,
Danny

> I need to execute a stored procedure and pass each of the above as
> parameters:

One method is to select the values into local variables and then pass those
as parameters. For example:

SELECT
@CustID = CustID,
@CustName = CustName,
@CustContact = CustContact,
@CustPhone = CustPhone
FROM dbo.Customers
WHERE CustID = 1

EXEC @ReturnCode = dbo.MyProcedure
@CustID = @CustID,
@CustName = @CustName,
@CustContact = @CustContact,
@CustPhone = @CustPhone

--
Hope this helps.

Dan Guzman
SQL Server MVP

<Daniel.Peaper@gmail.com> wrote in message
news:1170066045.614229.220350@j27g2000cwj.googlegr oups.com...
> Hi All,
>
> I am running SQL2000. Can anyone tell me how I can use the contents of
> a table as parameters for a stored procedure that resides on a
> different SQL server?
>
> I have a table:
>
> Customers:
> Cust_ID
> Cust_Name
> Cust_Contact
> Cust_Phone
>
> I need to execute a stored procedure and pass each of the above as
> parameters:
>
> @CustID, @CustName, @CustContact,@CustPhone
>
> The stored procedure also returns an error code.
>
> Thanks,
> Danny
>

On Jan 29, 10:40 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > I need to execute a stored procedure and pass each of the above as
> > parameters:
>
> One method is to select the values into local variables and then pass those
> as parameters. For example:
>
> SELECT
> @CustID = CustID,
> @CustName = CustName,
> @CustContact = CustContact,
> @CustPhone = CustPhone
> FROM dbo.Customers
> WHERE CustID = 1
>
> EXEC @ReturnCode = dbo.MyProcedure
> @CustID = @CustID,
> @CustName = @CustName,
> @CustContact = @CustContact,
> @CustPhone = @CustPhone
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> <Daniel.Pea...@gmail.com> wrote in message
>
> news:1170066045.614229.220350@j27g2000cwj.googlegr oups.com...
>
>
>
> > Hi All,
>
> > I am running SQL2000. Can anyone tell me how I can use the contents of
> > a table as parameters for a stored procedure that resides on a
> > different SQL server?
>
> > I have a table:
>
> > Customers:
> > Cust_ID
> > Cust_Name
> > Cust_Contact
> > Cust_Phone
>
> > I need to execute a stored procedure and pass each of the above as
> > parameters:
>
> > @CustID, @CustName, @CustContact,@CustPhone
>
> > The stored procedure also returns an error code.
>
> > Thanks,
> > Danny- Hide quoted text -
>
> - Show quoted text -

Thanks for that Dan,

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?

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx