Out of range error from C# to SQL

Question

I have a stored procedure as follows

create procedure [dbo].[PriceConfirm]
@quote float,
@membershipType int,
@promocode nvarchar(20),
@giftCertificateCode nvarchar(20)

as

if(LEN(@giftCertificateCode)>1)
begin
    declare @giftType int
    select @giftType = MembershipType from GiftCertificate where GiftCertificateCode=@giftCertificateCode
    if @giftType = @membershipType
    begin
        select 1 as result
    end
    else
    begin
        select 0 as result
    end
end
else
begin
    declare @total float
    select @total = Price from MembershipType where TypeID=@membershipType
    declare @discount float
    select @discount = 0
    if(LEN(@promocode)>1)
    begin
        select @discount = DiscountAmount from Membership_Promo where Promocode=@promocode and MembershipType = @membershipType
    end
    else
    begin
        select @discount=0
    end
    if ABS(@total-@discount-@quote) <.01
    begin
        select 1 as result
    end
    else
    begin
        select 0 as result
    end
end

And if I just execute the stored procedure in SQL Server Management Studio, it works.

exec PriceConfirm @quote=69.99, @membershipType=6, @promocode='1', @giftCertificateCode='1'

That returns 1, as it should.

But in C#, when I try to pass in the parameters @quote, @membershipType, @promocode, @giftCertificateCode with the exact same values, I get an exception in the code. It reads '69.99' is out of range.

In my table and in the stored procedures, I have the columns as floats. I just don't understand why passing in a C# double is giving me a precision error. Can anybody advise?

Edit:

Here's the C# code:

IDataAccess dataAccess = _dataAccessService.GetDataAccess();
        IDataConnection connection = _dataAccessService.GetConnection(Connectionstring);
        var operation = new DataOperation("PriceConfirm");
        operation.Parameters.Add(new DataParameter("@quote", DbType.Double, quote));
        operation.Parameters.Add(new DataParameter("@membershipType", DbType.Int32, membership));
        operation.Parameters.Add(new DataParameter("@promocode", DbType.String, promocode));
        operation.Parameters.Add(new DataParameter("@giftCertificateCode", DbType.String, giftcode));
        IResultSet reader = dataAccess.ExecuteResultSet(connection, operation, ResultSetType.Reader);

Reader is null after it tries to execute the operation. It throws an exception saying that "Data Parameter '69.99' is out of range."

Answer 1

OK you should NOT ever have numerics stored as floats if you do math calculations on them. You should fix your tables before doing anything else. Floats are exdtremely poor to use as they are inexact, always.

In your sp (which should not begin with sp_ BTW - that is for system procs and SQL Server will check the master datbase first every time it is run which is a waste of processing time)

Your sp does not accept float, it is defined as a decimal(4,2). Run Profiler and see what the C# code is trying to send to the database. I'll bet it is sending more than 2 decimal places.

Answer 2

It turns out changing the type to Decimal(18,4) does the trick. Apparently float types in SQL Server 2008 are kind of buggy. It would have worked sooner for me if I had remembered to change the DbType to Decimal in the C# code.