December 9, 2008 at 9:22 am
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,
December 9, 2008 at 9:30 am
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
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
December 9, 2008 at 10:38 am
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
December 9, 2008 at 10:55 am
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
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