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
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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 143

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 setup several different housekeeping processes for a SQL Server.

726 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