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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

808 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