Converion for VARCHAR to FLOAT

Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- SaratOriginally posted by sbaru
Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- Sarat
why do u need the table? what r u trying to do?

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b
PRINT @.a

it prints '0.4' for me|||Originally posted by sbaru
Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- Sarat

Yes, this is a common problem with all computers. When you declare float, the computer represents the numbers internally as approximations. The approximation is really really close to your number but often not exact.

To avoid this when working with monetary values, for example, I frequently will declare the field to be integer and just think of the value as pennies (for U.S. currency) and multiply the result by 100 to get dollars.

CONTAINS function and OR

I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan

Consuming Stored Procedure Output Param

This is my SProc:

CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @.numberRows */
@.selectDate datetime
,@.selectCountry int
,@.numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_smc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDate and RequestDateTime < dateadd(dd,1, @.selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @.selectCountry
ORDER BY configname

/* @.@.ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @.numberRows = @.@.RowCount

GO

And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue("@.selectDate", dateEntered)

myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))

Dim myParamAsNew SqlParameter("@.numberRows", SqlDbType.Int)

myParam.Direction = ParameterDirection.Output

myCommand.Parameters.Add(myParam)

myCommand.Connection = con

con.Open()

Dim readerAs SqlDataReader = myCommand.ExecuteReader()Dim rowCountAsInteger = reader.RecordsAffected

numberParts.Text = rowCount.ToString

con.Close()

EndSub

What should I fix?

label1.Text = myCommand.Parameters("@.numberRows").Value

|||

If I remember, I had this same problem, and found that you can't use the DataReader if you want to get the output parameter. I think you have to use DataSet.

|||

Read the following for an explanation of why it is happening and how to get around it.

http://p2p.wrox.com/archive/aspx/2001-12/24.asp

|||

How do I do the DataSet approach?

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection("Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx")

Dim dbDataSet =New DataSet()Dim dbAdapterAsNew SqlDataAdapter

dbAdapter.Fill(dbDataSet)

|||

You can do the following

Dim dbDataSet =New DataSet()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataSet,"tablename")

dbDataSet.Tables("tablename").rows.count

In case you have only one table, you can use a datatable instead of a dataset

Dim dbDataTable =New DataTable()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataTable)

dbDataTable.rows.count