CODEWITHSUNDEEP
sqlsql-server-2008-r2
ExceptionLimeCat
ExceptionLimeCat

Reputation: 6410

Is it possible to store output of calculation in select statement into a variable?

I need to perform a calculation with two values from a query and then store that into a variable. I am wondering how this can be done in SQL. This is what I've attempted thus far:

DECLARE @result DECIMAL
SELECT @result = val2 / val1 from table

There problem is the output is wrong. 

SELECT @result    
Output: 0

Where as if I don't use a variable the output is correct.

SELECT val2 / val1 from table
Output: 0.0712

Any ideas where I am going wrong here?

Upvotes: 1

Views: 4342

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

The default precision of a decimal is 0 (see here).

Try this:

DECLARE @result float;
SELECT @result = cast(val2 as float) / val1 from table;

SQL Server does integer division when both operands are integers. So, I'm casting it to float. If you really want decimal, then use a better declaration, such as:

DECLARE @result decimal(18, 6);

Finally, your selection is ambiguous when your table has multiple rows. I would suggest:

SELECT top 1 @result = cast(val2 as float) / val1 from table;

Better yet, add an order by clause so you know which row you are getting (unless you know the table has exactly one row).

Upvotes: 3

Diane
Diane

Reputation: 1

DECLARE @total dec(12,2), @num int SET @total = (SELECT SUM(Salary) FROM Employee)

Upvotes: 0

mr.Reband
mr.Reband

Reputation: 2430

Your DECIMAL type should be declared with precision. Depending on the data types of val1 and val2, you may also need casts on the val1 and val2 in the calculation, as in the code below: 

DECLARE @result DECIMAL(5,2)
SELECT @result = cast(val2 as decimal(5,2)) / cast(val1 as decimal(5,2)) from table
select @result

Upvotes: 1

Related Questions