Output Parameters

Bob Bedell · March 24th, 2006, 10:21 PM

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