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)));