CODEWITHSUNDEEP
sqlsql-server
Mohammed Ismail
Mohammed Ismail

Reputation: 196

Converting column to decimal in stored procedure

I have the following lines of code

Select 0 as Stipend

and then to actually add a value to stipend I do this

COALESCE(SUM(sdhi.stipend), 0, CONVERT(decimal(16, 4), 0)) Stipend

But it returns as 0 and not as a decimal for the columns, how can I convert this to decimal?

Upvotes: 2

Views: 217

Answers (2)

Pred
Pred

Reputation: 9042

0.000 is the same as 0. The DECIMAL data type does not define the formatting of the value, just how it is stored. This means, the representation of said value depends on the client software, like SSMS or a web app.

Formatting the value should be done by the client software (SSMS does it for you with it's current default settings, others might not)

This line is overcomplicated:

COALESCE(SUM(sdhi.stipend), 0, CONVERT(decimal(16, 4), 0)) Stipend

Besides that, the data type of literal value 0 is decimal(1,0), therefore the result will be decimal(1,0) when SUM(sdhi.stipend) is NULL (which can only happen if you have only NULL values in the group you are summing or the table is empty).

This is enough if you want to force DECIMAL(16,4) as the data type.

COALESCE(SUM(sdhi.stipend), CONVERT(decimal(16, 4), 0)) Stipend

Be aware, that the result of SUM(sdhi.stipend) might have different precision and scale (depending on the data you have stored in the column). Therefore you might want to consider the following:

CONVERT(decimal(16, 4), COALESCE(SUM(sdhi.stipend), 0)) AS Stipend

The problem with this is that when the result of SUM(sdhi.stipend) exceeds the limits of decimal(16, 4) you'll get overflow errors.

Choose wisely and read a bit about decimal arithmetic in SQL Server.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver15

Upvotes: 2

Amirhossein
Amirhossein

Reputation: 1251

SQL Server COALESCE() function returns the first non-null value in a list:

Your column sdhi.stipend data is NULL then your second argument is 0 then result is second argument.

If column sdhi.stipend data type is decimal or money you need this:

 Select COALESCE(SUM(sdhi.stipend), CONVERT(decimal(16,4), 0 )) as Stipend

Upvotes: 0

Related Questions