SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Unknown Error When Comparing Values In Stored Pro.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aadi
Starting Member

United Arab Emirates
3 Posts

Posted - 12/01/2010 :  08:15:11  Show Profile  Reply with Quote
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

Edited by - Aadi on 12/01/2010 08:40:27

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/01/2010 :  08:30:10  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 12/01/2010 08:33:44
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/01/2010 :  08:43:05  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 12/01/2010 08:43:47
Go to Top of Page

Aadi
Starting Member

United Arab Emirates
3 Posts

Posted - 12/01/2010 :  08:44:30  Show Profile  Reply with Quote
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
Go to Top of Page

Aadi
Starting Member

United Arab Emirates
3 Posts

Posted - 12/01/2010 :  10:51:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000