Syntax for returning an output parameter

Question

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 mytotal, 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.

Marek Grzenkowicz · Accepted Answer · 2010-12-13 09:54:35Z

1

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

edited Dec 13, 2010 at 9:54

answered Dec 13, 2010 at 7:29

Marek Grzenkowicz

17.4k11 gold badges88 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

devio Over a year ago

EXEC needs to add the @SiteID value

Meena Over a year ago

@Marek if my select statement contains more than 1 column , i.e, select RiskCount = sum(cnt), Exposure.SiteID then what shoyld be the syntax if my question is not so clear please have a look in to this stackoverflow.com/questions/43039801/… I am just waiting for the response

Collectives™ on Stack Overflow

Syntax for returning an output parameter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related