p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Output Parameters (http://p2p.wrox.com/showthread.php?t=40352)

Bob Bedell March 24th, 2006 10:21 PM

Output Parameters
 
Hello,

How do I call the following proc from Query Analyzer? Something like:

EXEC procOutGetCustomer 'ALFKI', ?, ?, ?

The proc is:

CREATE PROCEDURE procOutGetCustomer(
    @CustomerID nchar(5),
    @CompanyName nvarchar(40) OUTPUT,
    @ContactName nvarchar(30) OUTPUT,
    @ContactTitle nvarchar(30) OUTPUT)
AS SELECT
    @CompanyName = CompanyName,
    @ContactName = ContactName,
    @ContactTitle = ContactTitle
FROM
    Customers
WHERE
    CustomerID = @CustomerID
IF @@ROWCOUNT = 1
    RETURN 0
ELSE
    RETURN -1;

Thanks,

Bob




joefawcett March 25th, 2006 05:26 AM

You need to decalre variables to receive the output and specify that they are of type OUTPUT:
Code:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)
EXEC procOutGetCustomer 'ALFKI', @CompanyName OUTPUT, @ContactName OUTPUT, @ContactTitle OUTPUT
PRINT 'CompanyName: ' + @CompanyName
PRINT 'ContactName: ' + @ContactName
PRINT 'ContactTitle: ' + @ContactTitle

--

Joe (Microsoft MVP - XML)

Bob Bedell March 25th, 2006 06:32 AM

Thanks, Joe. Much appreciated.

Bob



All times are GMT -4. The time now is 10:19 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.