I am using a code similar to the one below inside my stored procedure:

declare @tempVar decimal 

select @tempVar = amount from tempTable where <<conditions>>
select @tempVar

now I am supposed to be getting 4.25 something and I get 4, when I can clearly see in the table that this particular value is not just 4. So why is the rounding take place? Anybody know what I am missing here?

Recommended Answers

All 3 Replies

It could likely be modified somewhere else in your store procedure that you did not post here. Also -- this is not a good idea:

IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test
Create Table #Test
  PK varchar(5) UNIQUE,
  Value1 decimal(5,2)


Insert Into #Test (PK, Value1) Values ('A', 4.25)
Insert Into #Test (PK, Value1) Values ('B', 4.5)


Declare @Temp decimal(5,2)
Select @Temp = Value1 From #Test Where PK <> ''
Print '(1) @Temp: ' + Cast(@Temp as varchar)


--Flip the table order
  PK Desc


Declare @Temp decimal(5,2)
Select @Temp = Value1 From #Test Where PK <> ''
Print '(2) @Temp: ' + Cast(@Temp as varchar)

Results in:

(1 row(s) affected)
(1 row(s) affected)
(1) @Temp: 4.50
(2) @Temp: 4.25

Next -- the decimal type is rounding because of how you defined it. See my above example for how I declare the decimal.

How you should go about this task:

Declare @Temp decimal(5,2)
Set @Temp = (Select Value1 From #Test Where PK <> '')

The above example returns an error where your code will use the last value.

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


how to round a coloumn in storedprocedure..
ex: total amount

silpa_1 Please repost your question as a new post, this thread is eight years old and you are unlikely to get much help by tacking a new question onto it...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.