Return Values and store those values for evaluation

  • Hi,

    I'd like to know what the best way to return multiple values from a stored procedure. I've looked at the OUTPUT function however I don't think I can do the following:

    This store proc is being called from another stored proc and the caller stored proc will use the return values for other calculations

    I want to be able to do something like the following

    [Code]

    EXEC dbo.GetAmounts

    @CompanyName = 'ACME',

    @Amount = @TheAmout OUTPUT,

    @Total = @TheTotAmount OUTPUT

    CREATE PROCEDURE dbo.GetAmounts(

    @CompanyName NVARCHAR(50),

    @Amount FLOAT OUTPUT,

    @Total FLOAT OUTPUT )

    AS

    SELECT @Amount = AmountValue,

    @TotalAmount = TotalAmount

    FROM TABLE

    WHERE CompanyName = @CompanyName

    [/Code]

    How can I accomplish the above using another technique? Someone mentioned using table variables but I've never used them and don't think this will solve my problem.

    Thanks,

  • Joe, the syntax of your sp and the statement calling it look exactly correct to me. Provided that the output params are DECLAREd before the sp is called, they should be correctly populated after it has executed. Have you tried it? I'm sorry if I've misunderstood your question!

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi,

    I appreciate the quick response. Anyhow what was happening was a spelling error on my part for the final sproc. I had

    @AcctN0 instead of of @AcctNo. Talk about a brain fart. I had typed in zero instead of o.

    Below is my final retrieval code if anyone is interested on how to get multiple outputs (obviously the set values are just dummy values)

    [Code]

    DECLARE @ReturnCode INT

    DECLARE @SCID INT,@AcctCode VARCHAR(50),@Wt FLOAT,@Origin VARCHAR(25),@Destination VARCHAR(25),@Rate INT,@TotAmt FLOAT,@IsMin VARCHAR(6)

    SET @SCID = 99999

    SET @AcctNo = 'SOMEVALUE'

    SET @Wt = 999999

    SET @Origin = 'A US State'

    SET @Destination = 'A US State

    EXEC @ReturnCode = usp_GetServiceLevelTariffInformation @SCID,@AcctCode,@Wt,@Origin,@Destination,@RateId = @Rate OUTPUT,@TotalAmount = @TotAmt OUTPUT,@IsMinCharge = @IsMin OUTPUT

    PRINT ' '

    PRINT 'Return code = ' + CAST(@ReturnCode AS CHAR(10))

    PRINT 'ID = ' + CAST(@Rate AS CHAR(10))

    PRINT 'Total Amount = ' + CAST(@TotAmt AS CHAR(10))

    PRINT 'Is Min Charge= ' + CAST(@IsMin AS CHAR(10))

    [/Code]

    Thanks

  • Hi Joe, I'n not reall sure what's going on there!

    Here's a simple test setup, with a sp taking 6 parameters, three of them being output params.

    It should be sufficient to put you on the right track.

    ALTER PROCEDURE dbo.TestOutputParams

    @Param1 VARCHAR(20), @Param2 VARCHAR(20), @Param3 VARCHAR(20),

    @Param4 VARCHAR(20) OUTPUT, @Param5 VARCHAR(20) OUTPUT, @Param6 VARCHAR(20) OUTPUT

    AS

    SET NOCOUNT ON

    SET @Param4 = @Param1 + ' CHANGED'

    SET @Param5 = @Param2 + ' CHANGED'

    SET @Param6 = @Param3 + ' CHANGED'

    RETURN

    GO

    ---------------------------------------------------------------------

    DECLARE @Parameter1 VARCHAR(20), @Parameter2 VARCHAR(20), @Parameter3 VARCHAR(20),

    @Parameter4 VARCHAR(20), @Parameter5 VARCHAR(20), @Parameter6 VARCHAR(20)

    SET @Parameter1 = 'param:1'

    SET @Parameter2 = 'param:2'

    SET @Parameter3 = 'param:3'

    EXEC dbo.TestOutputParams @Parameter1, @Parameter2, @Parameter3,

    @Parameter4 OUTPUT, @Parameter5 OUTPUT, @Parameter6 OUTPUT

    SELECT @Parameter4, @Parameter5, @Parameter6

    Results:

    -------------------- -------------------- --------------------

    param:1 CHANGED param:2 CHANGED param:3 CHANGED

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply