2

My tables consist of many decimal columns with different precision.
I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type instead. I verified most of the cases that I need can be done without using CAST or CONVERT. I only have one question left-
How can I explicitly reduce precision using udt (Last line in the script)

It there any other way a value could be converted into a UDT?
Is there any other approach to enforce using specific precision when defining a new column?

/*
DROP TABLE TestPrices;
DROP TABLE TestQty;
DROP TABLE TestResult;
DROP TYPE udtPrice;
DROP TYPE udtQty;
GO
*/

CREATE TYPE udtPrice
FROM DECIMAL(10, 4) NOT NULL;

CREATE TYPE udtQty
FROM DECIMAL(10, 6) NOT NULL;
GO

CREATE TABLE TestPrices (Price udtPrice)
CREATE TABLE TestQty    (Qty udtQty)
CREATE TABLE TestResult (Cost udtPrice)
GO

INSERT INTO TestPrices VALUES (23.1234)
INSERT INTO TestQty VALUES (2.123456)
INSERT INTO TestQty VALUES ('4.1234564353454354543543543')
select * from TestQty

INSERT INTO TestResult
SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q

SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q
SELECT * FROM TestResult

--how can I explicitly reduce precision using udt
SELECT TOP 1 CAST(Qty as udtPrice) FROM TestQty

Thank you in advance.

closed as unclear what you're asking by Md Haidar Ali Khan, Max Vernon, Vérace, Mr.Brownstone, John aka hot2use Sep 10 '18 at 6:31

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

I agree with the comment about providing a minimal, complete and verifiable example of your problem. I'm not sure why you're trying to use CAST/CONVERT when attempting to define a UserType. Simply use the base SystemType.

DROP TABLE IF EXISTS TestUdt;
DROP TYPE IF EXISTS DecimalPrecision5Scale4;
DROP TYPE IF EXISTS DecimalPrecision11Scale2;
GO

CREATE TYPE DecimalPrecision5Scale4
FROM DECIMAL(5, 4) NOT NULL;

CREATE TYPE DecimalPrecision11Scale2
FROM DECIMAL(11, 2) NOT NULL;
GO

CREATE TABLE TestUdt
(
TestCol1 DecimalPrecision5Scale4,
TestCol2 DecimalPrecision11Scale2
);

--Verify precision and scale of UDT

SELECT NAME
    ,precision
    ,scale
FROM sys.columns
WHERE NAME LIKE '%TestCol%';

| NAME     | precision | scale |
|----------|-----------|-------|
| TestCol1 | 5         | 4     |
| TestCol2 | 11        | 2     |

UPDATE - 2018-09-11 - Regarding OP comment

how can I explicitly reduce precision using udt

You could create a @temp Table Variable (or #temp table) with UDT columns for the precision you want, insert the raw data into the temp table (letting SQL Server do the conversion) and then select from the @temp table

Declare @TestConvert table(Qty udtPrice)
insert into @TestConvert select qty from TestQty
SELECT TOP 1 Qty from @TestConvert

| Qty    |
|--------|
| 2.1235 |
  • 1
    Thank you, @scott, for pointing me to the right direction. – DanielS Sep 11 '18 at 13:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.