Syntax for returning an output parameter

I have a stored procedure where I will have an output parameter. The query works and when I run the query inside SQL Server Management Studio, I get the correct answer. My problem is assigning the answer to my output parameter. Here is the stored procedure:

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

I try to assign @RiskCount to be the value in m开发者_开发知识库ytotal, but it says that the column doesn't exist. I just want to get that one result back. Shouldn't be too difficult, just a syntax thing that I can't get. Thanks.

Modify your query like this (the crucial part is the 1st line of the SELECT statement - 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

Execute it like this:

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