Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Precision on Money data type

How do I set the precision on the money data type in SQL Server. I want it to display only 2 decimal places and get rid of any more than that id someone tries to insert more without throwing an error.

Example:

i input 10.5432

It makes it 10.54
automatically.

If this is not possible, can I at least put in an amount like 10.56 and not have the database automatically turn this into 10.5600

Please help::How do I set the precision on the money data type in SQL Server.

Tried the documentation? Mean, this is so obvious that I would look there first.

Let me quote from "money data type / overview":

::Monetary data values from -2^63 (-922,337,203,685,477.5808) through
::2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary
::unit. Storage size is 8 bytes.

::I want it to display only 2 decimal places and get rid of any more than that id someone
::tries to insert more without throwing an error.

Welcome to programming. YOu have to do so in your input layer.

::If this is not possible, can I at least put in an amount like 10.56 and not have the database
::automatically turn this into 10.5600

You can us an insert / update trigger to round the values. I would normally handle this i n the business objects :-)|||Use decimal instead of money. You can exactly specify the precision and scale|||Thanks Dutch,... I appreciate that

Precision and Scale in a calulated column

How do I set Precision and Scale in a calulated column?

I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please?

Thanks,

Scott

hi Scott,

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE #t (

d decimal(18,4) DEFAULT 1234.123,

D2 AS CONVERT(decimal(8,2), d)

);

GO

INSERT INTO #t DEFAULT VALUES;

SELECT * FROM #t;

GO

DROP TABLE #t;

--<

d D2

--

1234.1230 1234.12

regards

|||

Either using a CREATE TABLE or ALTER TABLE statement, and add the precision,scale to the computed column declaration.

Here is an example:

CREATE TABLE #MyTable
( RowID int IDENTITY,
Cost decimal(6,2),
Quantity int,
Total AS cast(( Cost * Quantity ) AS decimal(8,4))
)

INSERT INTO #MyTable VALUES ( 5, 10 )
INSERT INTO #MyTable VALUES ( 2.5, 5 )
INSERT INTO #MyTable VALUES ( 4.55, 5 )

SELECT * FROM #MyTable

DROP TABLE #MyTable

|||

Thank you for helping.

I'm using the Management Studio. Is there a way to accomplish this in the formula line?

Thanks again.

Scott

|||

Thanks for helping.

I tried the following:

***********************************

Use SIR
ALTER TABLE dbo.Table_1
ALTER COLUMN Results
nPRECISION(6,2)

Go

************************************

Recieved the following message:

************************************

Msg 4928, Level 16, State 1, Line 2
Cannot alter column 'Results' because it is 'COMPUTED'.

What else might I try?

|||

You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula.

Do something like this:

USE SIR;

ALTER TABLE dbo.Table_1
DROP COLUMN Results;

ALTER TABLE dbo.Table_1
ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2)));

precision

Precision is the number of digits in a number. Scale is the number of digits
to the right of the decimal point in a number. For example, the number
123.45 has a precision of 5 and a scale of 2.
Is the above statement true? or Is the precision 3 and the scale is 2?
Thank you in advance.In your example 123.45 the precision (p) is 5 and scale (s) is 2.
For more information:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
--
Rohtash Kapoor
http://www.sqlmantra.com
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> Precision is the number of digits in a number. Scale is the number of
digits
> to the right of the decimal point in a number. For example, the number
> 123.45 has a precision of 5 and a scale of 2.
> Is the above statement true? or Is the precision 3 and the scale is 2?
> Thank you in advance.
>|||So on this one 1234567890.1234 the precision is 14 and the scale is 4 same
as (14,4)?
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
> In your example 123.45 the precision (p) is 5 and scale (s) is 2.
> For more information:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> > Precision is the number of digits in a number. Scale is the number of
> digits
> > to the right of the decimal point in a number. For example, the number
> > 123.45 has a precision of 5 and a scale of 2.
> >
> > Is the above statement true? or Is the precision 3 and the scale is 2?
> >
> > Thank you in advance.
> >
> >
>|||Yes
>--Original Message--
>So on this one 1234567890.1234 the precision is 14 and
the scale is 4 same
>as (14,4)?
>"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in
message
>news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
>> In your example 123.45 the precision (p) is 5 and scale
(s) is 2.
>> For more information:
>> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-
dz_3grn.asp
>> --
>> Rohtash Kapoor
>> http://www.sqlmantra.com
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in
message
>> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
>> > Precision is the number of digits in a number. Scale
is the number of
>> digits
>> > to the right of the decimal point in a number. For
example, the number
>> > 123.45 has a precision of 5 and a scale of 2.
>> >
>> > Is the above statement true? or Is the precision 3
and the scale is 2?
>> >
>> > Thank you in advance.
>> >
>> >
>>
>
>.
>