SP rounds decmial (18,2) value

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 2005 Forums

Transact-SQL (2005)

SP rounds decmial (18,2) value

Author

Topic

droog
Starting Member

12 Posts

Posted - 2007-06-26 : 03:07:08

I apologize for this silly question, but it is driving me up the wall. I have a couple params that are decimals and i am defining the decimal (18,2), but i don't understand why SQL keeps rounding it. The datatype of my field is the same, if i enter the value directly into the database it does not round. How do i get this to work? thanks!


PROCEDURE [dbo].[sp_Location_Create]
	@locPrefix varchar(10),
	@locShelfNum varchar(10),
	@locDesc varchar(75),
	@locShelfSpace decimal(18, 2),
	@locAvailShelfSpace decimal(18, 2)
AS
	INSERT INTO dbo.Location(
		locPrefix,
		locShelfNum,
		locDesc,
		locShelfSpace,
		locAvailShelfSpace)
	VALUES(
		@locPrefix,
		@locShelfNum,
		@locDesc,
		@locShelfSpace,
		@locAvailShelfSpace)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-26 : 03:18:10

Rounding upto what decimal places? Can you give an example of how you are passing the values to SP? and what is getting stored in the table as a result of SP?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-26 : 03:18:53

Hard to tell without seeing the data you send to the sp.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-26 : 03:19:40

What is the datatype of the value BEFORE you send it to the SP?
float?

Peter Larsson
Helsingborg, Sweden

droog
Starting Member

12 Posts

Posted - 2007-06-26 : 09:14:25

The datatype is a decimal and its rounding to the nearest integer. So passing 32.50 is being stored as 33. Passing 3.41 is being stored as 3. My database is set to the correct datatype, and if i enter the values manually, they stay as decimals.


<cfstoredproc datasource="amazon" procedure="sp_Location_Create" >
	<cfprocparam dbvarname="@locPrefix" value="BKSH001" cfsqltype="cf_sql_varchar" type="in">
	<cfprocparam dbvarname="@locShelfNum" value="001" cfsqltype="cf_sql_varchar" type="in">
	<cfprocparam dbvarname="@locDesc" value="BKSH001" cfsqltype="cf_sql_varchar" type="in">
	<cfprocparam dbvarname="@locShelfSpace" value="29.50" cfsqltype="cf_sql_decimal" type="in">
	<cfprocparam dbvarname="@locAvailShelfSpace" value="32.50" cfsqltype="cf_sql_decimal" type="in">
</cfstoredproc>

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-26 : 10:17:50

cfsqltype="cf_sql_decimal"
Looks like Cold Fusion.
Any way to tell it to use 2 decimal places?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

droog
Starting Member

12 Posts

Posted - 2007-06-26 : 10:26:52

That is probably the issue. Just found that there is a scale property for use with a decimal. Hope that solves it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources