Rounding a decimal value with dynamic variable

Question

I need to round a value of a column in table A based on another column in table B in a SQL function.

DECLARE @currencyround INT

SELECT @currencyround = ISNULL(currencyround, 2) 
FROM dbo.PRTL_currencySettings

When I put value of @currencyround directly in the query like the following, it's working fine:

SELECT
    CAST(POS.BALANCE AS DECIMAL(18, 2)) AS DBAmount
FROM 
    dbo.POS_SALES POS

When I put value of @currencyround like the following, it's showing error:

Incorrect syntax near '@currencyround'.

SELECT
    CAST(POS.BALANCE AS DECIMAL(18, @currencyround)) AS DBAmount
FROM 
    dbo.POS_SALES POS  

Answer 1

If you need specific metadata you could use dynamic SQL:

DECLARE @currencyround int;
SELECT @currencyround=ISNULL(currencyround,2) FROM dbo.PRTL_currencySettings;

DECLARE @sql NVARCHAR(MAX) = 
N'select CAST(POS.BALANCE AS DECIMAL(18,<currencyround>)) AS DBAmount
FROM dbo.POS_SALES POS';

SET @sql = REPLACE(@sql, '<currencyround>', @currencyround);

EXEC(@sql);

But personally I would not write such code. I would rather format the number in the application layer.