Returning the result of a stored procedure to SQL variable

Question

I have a stored procedure entitled GetLatestUsageFactor801UNR which returns a decimal value with precision of 18,10 but when I used the code below I am always being returned a zero, how can I set the result of the stored procedure into the variable as when selecting after the fact it remains at zero

DECLARE @UsageFactor801UNR decimal(18,10)
EXEC @UsageFactor801UNR = GetLatestUsageFactor801UNR
SELECT @UsageFactor801UNR

I am using SQL Server as my RDBMS

My stored procedure is as follows:

select TOP 1 TotalUsageFactor as '801 UNR Usage Factor'
from MarketMessage as a
inner join messagetype591 as b on a.MarketMessageID = b.MarketMessageID
inner join AdditionalAggregationInformation as c on b.MessageType591ID = c.MessageType591ID
inner join AdditionalAggregationData as d on c.AdditionalAggregationInformationID = d.AdditionalAggregationInformationID
where SettlementRunIndicator = 20
and LoadProfileCode = 801
and TimeOfUse = 'UNR'
order by SettlementDate desc

Then you should know that stored procedures return an int. If you have `decimal(18,10)`, that has to be an [output parameter](https://stackoverflow.com/a/10275757/11683). — GSerg, May 09 '19 at 11:56
How do I specify an output parameter? Is this done in the declaration of the variable or on the stored procedure being called? — Seán-Pól, May 09 '19 at 11:57
Please run the procedure individually first & see the output. Also, you can verify the SP code by "sp_helptext" syntax. https://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable — Prateek Kumar Dalbehera, May 09 '19 at 12:02
I am new to SQL server so this is all new to me apologies, I have updated my post to show the Stored Procedure — Seán-Pól, May 09 '19 at 12:09

Zhorov · Answer 1 · 2019-05-09T19:45:44.180

With your current execution you get the return value from execution of the stored procedure, which is an integer.

One option here is to define an output parameter to retrieve the value from your statement:

-- Stored procedure
CREATE PROCEDURE [GetLatestUsageFactor801UNR]
    @UsageFactor801UNR decimal(18, 10) OUTPUT
AS BEGIN
    select TOP 1 @UsageFactor801UNR = TotalUsageFactor
    from MarketMessage as a
    inner join messagetype591 as b on a.MarketMessageID = b.MarketMessageID
    inner join AdditionalAggregationInformation as c on b.MessageType591ID = c.MessageType591ID
    inner join AdditionalAggregationData as d on c.AdditionalAggregationInformationID = d.AdditionalAggregationInformationID
    where SettlementRunIndicator = 20
        and LoadProfileCode = 801
        and TimeOfUse = 'UNR'
    order by SettlementDate desc
END

-- Execution    
DECLARE @err int
DECLARE @UsageFactor801UNR decimal(18, 10)

EXECUTE @err = [GetLatestUsageFactor801UNR] @UsageFactor801UNR OUTPUT
IF @err = 0 BEGIN
    PRINT 'OK'
    PRINT @UsageFactor801UNR
    END
ELSE BEGIN
    PRINT 'Error'
END

Another option is to store the results from this stored procedure in a table. Then you don't need an output parameter:

-- Stored procedure
CREATE PROCEDURE [GetLatestUsageFactor801UNR]
AS BEGIN
    select TOP 1 TotalUsageFactor AS UsageFactor801UNR
    from MarketMessage as a
    inner join messagetype591 as b on a.MarketMessageID = b.MarketMessageID
    inner join AdditionalAggregationInformation as c on b.MessageType591ID = c.MessageType591ID
    inner join AdditionalAggregationData as d on c.AdditionalAggregationInformationID = d.AdditionalAggregationInformationID
    where SettlementRunIndicator = 20
        and LoadProfileCode = 801
        and TimeOfUse = 'UNR'
    order by SettlementDate desc
END

-- Execution

DECLARE @UsageFactor801UNR decimal(18, 10)

CREATE TABLE #Temp (UsageFactor801UNR decimal(18, 10))
INSERT INTO #Temp (UsageFactor801UNR)
EXECUTE [GetLatestUsageFactor801UNR]

SELECT @UsageFactor801UNR = UsageFactor801UNR 
FROM #Temp

PRINT @UsageFactor801UNR

Returning the result of a stored procedure to SQL variable

1 Answers1