Solved

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

Posted on 2008-10-28
5
318 Views
Last Modified: 2008-10-29
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.
0
Comment
Question by:aneethat
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22819844
is it possible that @iprevyearEx gets a value of 0 ?
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22820004
You never set a value for @ishowyearEx .  

Both of your SET commands change the value of @iprevyearEx
0
 

Author Comment

by:aneethat
ID: 22820082
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 22824723
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22824737
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now