stored procedure return .00 decimal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure return .00 decimal

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 14:09:11

This procedure does not return cents ... I tried to change @sale to float but it is same ...

Does anybody have idea why?

CREATE PROCEDURE PaymentSumary
@sale money OUTPUT,
@fdate datetime,
@tdate datetime
AS
BEGIN
SET NOCOUNT ON
SELECT @sale=SUM([Amount Paid])
FROM [arhPOSInvoicePayments]
WHERE [Date Paid] BETWEEN @fdate AND @tdate
END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 14:11:58

What is the datatype of [Amount Paid] column?

Madhivanan

Failing to plan is Planning to fail

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 14:17:41

money

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 14:20:52

Also, for some reason it returns rounded integer .. For example $0.65 return as $1.00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 14:21:58

quote:
Originally posted by ilimax

Also, for some reason it returns rounded integer .. For example $0.65 return as $1.00

Check the values for that column whether it has all values rounded

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 14:24:19

ilimax, I'm not getting the same results as you. I get the proper money value returned. Check this:


create table test (column1 money)
go

insert into test values (0.65)
insert into test values (19.95)
insert into test values (4.32)
go

CREATE PROCEDURE spTest
@sale money OUTPUT
AS
BEGIN
SET NOCOUNT ON
SELECT @sale=SUM(column1) 
FROM test
END
go

declare @sale money

exec sptest @sale = @sale OUTPUT

print @sale

drop proc spTest
drop table test

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 14:28:03

No, values are saved good ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 14:29:09

quote:
Originally posted by ilimax

No, values are saved good ...

Did you run it in Query Analyser?

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 14:29:29

Where are you seeing the incorrect value being returned? In your application or in Query Analyzer? Look at my test to see that Query Analyzer is returning the correct value. Are you using the correct data type in your application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 14:31:01

I found where is problem .... In my application ...

However, thank you very much for your responses ..

ilimax
Posting Yak Master

164 Posts

Posted - 2008-06-23 : 15:52:56

This is what was actually problem ...

My application is build in C#. When I set output parameter for ADO.net command object I used
Paramter.SqlDbType = SqlDbType.Decimal

When I catch result object Parameter.Value() I Convert.ToDecimal that value and for some reason program convert to integer.

I change this Paramter.SqlDbType = SqlDbType.Decimal to Paramter.SqlDbType = SqlDbType.Float
... and I Convert.ToDouble(Parameter.Value result) ...and now works fine ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 16:07:33

Note that when you use Decimal datatype you should use proper precision. Otherwsie decimal values would be truncated

declare @d decimal
set @d=1234.56
select @d

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources