返回输出参数的语法

Question

我有一个存储过程，我将有一个输出参数。查询工作正常，当我在SQL Server Management Studio中运行查询时，我得到了正确的答案。我的问题是分配答案给我的输出参数。这里是存储过程：

ALTER PROCEDURE [dbo].[RDusp_Report_Impact] 
-- Add the parameters for the stored procedure here 
@SiteID int, 
@RiskCount int output 
AS 
BEGIN 
SET NOCOUNT ON; 



select sum(cnt) as mytotal from 
(
select count(Impact.Rating) as cnt from Impact, Likelihood, Exposure where 
Impact.SiteID=2 
and Exposure.SiteID = 2 and Impact.Rating > 3 and Likelihood.Rating > 3 
and Exposure.ImpactID = Impact.ImpactID and exposure.LikelihoodID = Likelihood.LikelihoodID 
) as c 


END 

我尝试分配@RiskCount是在mytotal的价值，但它说，列不存在。我只想得到一个结果。不应该太困难，只是一个我无法得到的语法。谢谢。

Answer 1

修改您的查询像这样（的关键部分是SELECT语句的第一行 - select @RiskCount = sum(cnt)：

ALTER PROCEDURE [dbo].[RDusp_Report_Impact] 
-- Add the parameters for the stored procedure here 
@SiteID int, 
@RiskCount int output 
AS 
BEGIN 
    SET NOCOUNT ON; 

    select @RiskCount = sum(cnt) 
    from 
    (select count(Impact.Rating) as cnt 
    from Impact, Likelihood, Exposure 
    where Impact.SiteID=2 
    and Exposure.SiteID = 2 
    and Impact.Rating > 3 
    and Likelihood.Rating > 3 
    and Exposure.ImpactID = Impact.ImpactID 
    and exposure.LikelihoodID = Likelihood.LikelihoodID) as c 
END 

执行这样的：

DECLARE @rc int 
EXEC [dbo].[RDusp_Report_Impact] 123, @rc output -- 123 is an example @SiteID value 
SELECT @rc