Executing Stored Procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel.Peaper@gmail.com - 29 Jan 2007 10:20 GMT

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

Reply to this Message

Dan Guzman - 29 Jan 2007 12:40 GMT

> 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

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Hi All,
>
[quoted text clipped - 19 lines]
> Thanks,
> Danny

Reply to this Message

Daniel.Peaper@gmail.com - 30 Jan 2007 03:18 GMT

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:
[quoted text clipped - 51 lines]
>
> - 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?

Reply to this Message

Erland Sommarskog - 30 Jan 2007 11:28 GMT

> 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

Signature

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

Reply to this Message