Solved

Decimal places not returning from a stored procedure

Posted on 2001-07-11
9
415 Views
Last Modified: 2012-06-27
I have a stored procedure which should be returning a percentage back to the ASP page.

The problem is it is rounding up to the nearest whole number and not returning the decimal places.


The relevant parts of the stored procedure are as follows(the variable used to return the percentage is @result4):

CREATE PROCEDURE sp_highlevelsummaryrpt

@result4 int output
AS
declare @discinc decimal


if (@discinc is null)
          begin
          set @discinc = '0'
     end

set @discinc = (select Approved_Disc_Inc from pr_misc)

set @result4 = @discinc
select @result4

In the ASP page, I am showing the variable on the page as follows:


result4 = cmd_Execute_stored_procedure.Parameters("@result4")

%><td><%Response.Write(formatnumber(result4,2))%></td><%

I have tried changing the data type in the stored to float, numeric but none work.

Can anyone help??
0
Comment
Question by:obicham
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 6272822
you have declared your output variable (@result4) as an integer, therefore you will not get any decimalplaces returned.

try this: @return4 numeric(13,10)
0
 
LVL 11

Expert Comment

by:Otana
ID: 6272832
I meant @result4 off course, sorry...
0
 

Author Comment

by:obicham
ID: 6272847
That's a very good point but unfortunately it still doesn't work for some reason.

I tried declaring the output variable as decimal and a float but that won't work either
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 11

Expert Comment

by:Otana
ID: 6272881
set @discinc = (select Approved_Disc_Inc from pr_misc)

what type of field is Approved_Disc_Inc? And what does it contain?
0
 

Author Comment

by:obicham
ID: 6272927
It is decimal(5) and it contains the value 1.75
0
 
LVL 11

Expert Comment

by:Otana
ID: 6272949
select Approved_Disc_Inc from pr_misc

does this return a single value? or multiple records? can you post more of your stored procedure?
0
 
LVL 7

Accepted Solution

by:
John844 earned 50 total points
ID: 6273202
whenyou tried changing the datatype as mentioned above, did you also change the datatype of your command parameter?
0
 
LVL 7

Expert Comment

by:John844
ID: 6273214
does the stored procedure display the proper values when you run it from query analyser?

I would also change this line to avoid any future problems if more records are added.
set @discinc = (select Approved_Disc_Inc from pr_misc)
to
set @discinc = (select TOP 1 Approved_Disc_Inc from pr_misc)
0
 

Author Comment

by:obicham
ID: 6276103
This is indeed where I was going wrong.

Thanks very much.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 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