Unknown Error When Comparing Values In Stored Pro.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unknown Error When Comparing Values In Stored Pro.

Author

Topic

Aadi
Starting Member

3 Posts

Posted - 2010-12-01 : 08:15:11

Hi All,
I have a stored procedure, which I'm calling from ASP.Net application. I'm facing a problem in stored procedure when comparing values.
Please check code below to get further details.

Stored Procedure:
=========================================
ALTER PROCEDURE [dbo].[MyStoredProcedure]
(@ItemID nvarchar(50), @Weight1 float, @Weight2 float)

-- check remaining/available weight in items table
DECLARE @RemainingWeight float
SET @RemainingWeight = (SELECT RemainingWeight FROM items WHERE ItemID = @ItemID)

-- if supplied (@Weight1 + @Weight2) > @RemainingWeight , throw error
IF (@Weight1 + @Weight2) > @RemainingWeight
BEGIN
DECLARE @Error nvarchar(2000)
SET @Error = 'Weight1: ' + CAST(@Weight1 AS nvarchar(50)) + ', Weight2: ' + CAST(@Weight2 AS nvarchar(50)) + ', Remaining/Available Weight: ' + CAST(@RemainingWeight AS nvarchar(50)) + ', Total Supplied Weight: ' + CAST((@Weight1 + @Weight2) AS nvarchar(50))
RAISERROR(@Error, 16, 1)
Return -1
END
..................................
..................................
=========================================

Supplied Values Through ASP.Net Page:
=========================================
Weight1 = 0.67
Weight2 = 0
Data type is double i.e. DbType.Double
=========================================

When I run the application, It goes into IF block (stored procedure) even if (@Weight1 + @Weight2) <= @RemainingWeight, and raise error.
Error:
=========================================
Error Message: Weight1: 0.67, Weight2: 0, Remaining/Available Weight: 0.67, Total Supplied Weight: 0.67
=========================================

The IF condition should not be true in this scenario because supllied values is equal to reamining/available weight, i.e.
Weight1 + Weight2 = (0.67 + 0 ) = 0.67
Remaining/Available Weight = 0.67

I don't know what and where is the problem :(
Please help me out if you have any idea.

Thank you so much for your time and interest :)

Adnan Shaikh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-01 : 08:30:10

Floats are approximate datatypes - suspect you might be having issues with that.
Could convert to decimal for the check.

It's missing the total supplied weight in the error message - are you sure this is the SP that you are running?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-01 : 08:43:05

try this
declare @i float
select @i = 4.4
select convert(decimal(30,28),@i)
select convert(varchar(50),@i)

and in your case
declare @i float, @j float
select @i = 0.67, @j = 0.66999999999
select 1 where @i <= @j
select convert(varchar(20),@i), convert(varchar(20),@j)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Aadi
Starting Member

3 Posts

Posted - 2010-12-01 : 08:44:30

Thank you so much for your quick response and help.

Let me change the data type, Float to Decimal.

Yes, This is the same SP. 'Total Supplied Weight' It was a typo, sorry for that.

Adnan Shaikh

Aadi
Starting Member

3 Posts

Posted - 2010-12-01 : 10:51:29

I modified the SP, After converting into decimal, its passed through IF block. but after that, when it updates the items table, its not updating properly. Let me explain you through code.

Stored Procedure:
=========================================
ALTER PROCEDURE [dbo].[MyStoredProcedure]
(@ItemID nvarchar(50), @Weight1 float, @Weight2 float)

-- check remaining/available weight in items table
DECLARE @RemainingWeight decimal -- it was Float before
SET @RemainingWeight = (SELECT RemainingWeight FROM items WHERE ItemID = @ItemID)

-- calculate total weight in a seperate variable
DECLARE @TotalSuppliedWeight decimal
SET @TotalSuppliedWeight = @Weight1 + @Weight2

-- if supplied @TotalSuppliedWeight > @RemainingWeight , throw error
IF @TotalSuppliedWeight > @RemainingWeight
BEGIN
DECLARE @Error nvarchar(2000)
SET @Error = 'Weight1: ' + CAST(@Weight1 AS nvarchar(50)) + ', Weight2: ' + CAST(@Weight2 AS nvarchar(50)) + ', Remaining/Available Weight: ' + CAST(@RemainingWeight AS nvarchar(50)) + ', Total Supplied Weight: ' + CAST(@TotalSuppliedWeight AS nvarchar(50))
RAISERROR(@Error, 16, 1)
Return -1
END

..................................
..................................

-- update items table
UPDATE items SET RemainingWeight = RemainingWeight - @TotalSuppliedWeight WHERE ItemID = @ItemID

=========================================

Now consider RemainingWeight is 0.67 .

Supplied Values Through ASP.Net Page:
=========================================
Weight1 = 0.67
Weight2 = 0
Data type is double i.e. DbType.Double
=========================================

When I run the application (on remote server), how it updates
Remaining Weight: -0.Something <--- its less than zero. it should be zero.

By the way, I'm facing this problem on remote server. I'm using SQL Server 2005 Express Edition on remote server. On my local server, everything is working fine.

One more thing, when I converted back decimal variables into float, it worked fine.

Any idea? Why and where things are going wrong?

Thanks for your kind support :)

Adnan Shaikh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources