SQL 2000 stored procedure giving error message : Msg 8624, Level 16, State 16, Procedure rx_prRetentionPercentage, Line 9

Hi,

        I have written a stored procedure to calculate percentage. i use output variable as stored procedure parameter. I don't get any syntax error but i got th following error

Server: Msg 8624, Level 16, State 16, Procedure rx_prRetentionPercentage, Line 9
Internal SQL Server error.

my stored proedure as follows. I pass parameter 2008, 2007, 'MMA', 0


CREATE PROCEDURE rx_prRetentionPercentage(@ishowyear INT, @iprevyear INT,  @chshowcd char(10),  @Percent int output)

AS


DECLARE @ishowyearEx int, @iprevyearEx int

SET  @iprevyearEx= (SELECT Count(*) as TotalRecord FROM  rx_vExhibitorCompanyFr2004
where chshowcd=@chshowcd and  ishowyear=@ishowyear and iqtyordered NOT IN(0) and chorderstatus in('C'))

SET  @iprevyearEx= (SELECT Count(*) as TotalRecord FROM  rx_vExhibitorCompanyFr2004
where chshowcd=@chshowcd and  ishowyear=@iprevyear and iqtyordered NOT IN(0) and chorderstatus in('C'))

set @Percent = @ishowyearEx/@iprevyearEx


return @percent

GO


Could anyone help me on that? Thank you.
aneethatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
is it possible that @iprevyearEx gets a value of 0 ?
0
aflockhartCommented:
You never set a value for @ishowyearEx .  

Both of your SET commands change the value of @iprevyearEx
0
aneethatAuthor Commented:
Sorry. I don't get any value i get error message even after i chaged the @iprevyearEx to @ishowyearEx.

I tried removing the lines buy keeping only the set command even without output parameter, but gives the same error. Hope the problem is on my set command.

Could anyone help me?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
CREATE PROCEDURE rx_prRetentionPercentage(@ishowyear INT, @iprevyear INT,  @chshowcd varchar(10),  @Percent decimal(10,2) output)
AS
DECLARE @ishowyearEx decimal(10,2), @iprevyearEx decimal(10,2) 
SELECT @ishowyearEx = SUM(CASE WHEN ishowyear=@ishowyear THEN 1 ELSE 0 END)  
     , @iprevyearEx = SUM(CASE WHEN ishowyear=@iprevyear THEN 1 ELSE 0 END)  
  FROM  rx_vExhibitorCompanyFr2004
  where chshowcd=@chshowcd 
    and ishowyear IN ( @ishowyear , @iprevyear )
    and iqtyordered NOT IN(0) 
    and chorderstatus in('C') 
if @iprevyearEx = 0
  set Percent = 0
else
  set @Percent = @ishowyearEx/@iprevyearEx 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and a call example would be:


declare @result decimal(10,2)
exec rx_prRetentionPercentage 2008, 2007, 'MMA', @result OUTPUT
select @result

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.