Test it
DECLARE @val DECIMAL(10,6) = 987.000000
SELECT CONVERT(INT, @val) AS Intgr, CONVERT(DECIMAL(10,2), @val) Dec2, CONVERT(DECIMAL(10,0), @val) Dec0
and find out why... Just kidding ;)
You should convert to the destination data type.
Have a look here:
ROUND (sql)[
^]
Round function is working but returns the same data type ;)
[EDIT]
In your case i suggest to create stored procedure like this (using dynamic query):
CREATE PROCEDURE GetStoreArticle
@id INT
AS
BEGIN
DECLARE @dec INT
SELECT @dec = show_decimals FROM measures AS m INNER JOIN store AS s ON m.ID = s.measurement_id WHERE s.id = @id
DECLARE @sql NVARCHAR(MAX) = N'
SELECT s.id, s.item, CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) + '), s.count_left_in_store) AS UnitsLeft
FROM store AS s INNER JOIN measures AS m ON s.measurement_id = m.ID
WHERE s.id = ' + CONVERT(VARCHAR(10),@id)
EXEC (@sql)
END
Sample code used for test:
CREATE TABLE #measures(ID INT IDENTITY(1,1), measure VARCHAR(30), show_decimals INT)
INSERT INTO #measures (measure, show_decimals)
VALUES('unit',0), ('kilo',3), ('meter', 2)
CREATE TABLE #store (id INT IDENTITY(1,1), item VARCHAR(30), measurement_id INT, count_left_in_store DECIMAL(16,6))
INSERT INTO #store (item,measurement_id,count_left_in_store )
VALUES('pencil',1 , 987), ('sugar', 2, 5879), ('UDP cable', 3, 22)
DECLARE @id INT = 3
DECLARE @dec INT
SELECT @dec = show_decimals FROM #measures AS m INNER JOIN #store AS s ON m.ID = s.measurement_id WHERE s.id = @id
DECLARE @sql NVARCHAR(MAX) = N'
SELECT s.id, s.item, CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) + '), s.count_left_in_store) AS UnitsLeft
FROM #store AS s INNER JOIN #measures AS m ON s.measurement_id = m.ID
WHERE s.id = ' + CONVERT(VARCHAR(10),@id)
EXEC (@sql)
DROP TABLE #store
DROP TABLE #measures
It works as well ;)