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
319 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 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

20 Experts available now in Live!

Get 1:1 Help Now