sql stored procedure rounding off?

[progtick]

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

declare @tempVar decimal 

select @tempVar = amount from tempTable where <<conditions>>
IF (CONDITIONAL STATEMENT)
BEGIN
select @tempVar
END

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?

[sknake]

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)
)

GO

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

GO

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

GO

--Flip the table order
CREATE CLUSTERED INDEX IX_Test_1 ON #Test
(
  PK Desc
)

GO

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.

[silpa_1]

hii

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

[happygeek]

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...