将存储过程的结果返回给SQL变量
将存储过程的结果返回给SQL变量
提问于 2019-05-09 19:39:51
我有一个名为GetLatestUsageFactor801UNR的存储过程，它返回一个精度为18,10的十进制值，但是当我使用下面的代码时，我总是返回一个零，我如何将存储过程的结果设置到变量中，因为在选择后它仍然是零
DECLARE @UsageFactor801UNR decimal(18,10)
EXEC @UsageFactor801UNR = GetLatestUsageFactor801UNR
SELECT @UsageFactor801UNR我使用SQL Server作为我的RDBMS
我的存储过程如下：
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回答 1
Stack Overflow用户
发布于 2019-05-09 20:38:03
对于当前的执行，您将从存储过程的执行中获得返回值，它是一个整数。
这里的一种选择是定义一个输出参数来从语句中检索值：
-- 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另一种选择是将此存储过程的结果存储在表中。这样就不需要输出参数了：
-- 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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56058606
复制相关文章
相似问题