Executing Stored Procedures

[Daniel.Peaper]

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

[Dan Guzman]

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

<Da***********@gmail.comwrote in message
news:11**********************@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

[Daniel.Peaper]

On Jan 29, 10:40 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.netwrote:

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

<[e-mail] in message

news:11**********************@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?

[Erland Sommarskog]

Da***********@gmail.com (Da***********@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, es****@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