Syntax question

[Wescotte]

Say I use the following SQL statement to create a table

CREATE TABLE BATCH (
BATCHNUM NUMERIC(5) NOT NULL,
COMPID CHAR(2) NOT NULL,
AMOUNT NUMERIC(9,2) NOT NULL,
BDATE DATE NOT NULL,
EDIREF CHAR(15),
FLAG CHAR(1) NOT NULL )

What is the exactly different between these two select statements

SELECT * FROM BATCH WHERE COMPID = '1A' AND AMOUNT = '123.45'
SELECT * FROM BATCH WHERE COMPID = '1A' AND AMOUNT = 123.45

Now, my logic was that if I passed a real number as a string value ie
'123.45' ( 0x3132332E343500 char array of 7 bytes long) that the
database would be much better at accurately (since not all real numbers
can be 100% accurately represented in binary) represently the value
itself verus the client. Am I completely off base with my logic?

The reason I ask is because when I perform the first statement more
than twice in a row it says no rows found however the 2nd statement
works every time. If I close my ODBC connection and restart it and I
can repeat the statement over and over and get the correct results. Why
is this?

Should I never pass any values besides strings using ''? If so why
doesn't the database return an error with my SQL statement?

Eric

[Erland Sommarskog]

Wescotte (we******@earthlink.net) writes:

Say I use the following SQL statement to create a table

CREATE TABLE BATCH (
BATCHNUM NUMERIC(5) NOT NULL,
COMPID CHAR(2) NOT NULL,
AMOUNT NUMERIC(9,2) NOT NULL,
BDATE DATE NOT NULL,
EDIREF CHAR(15),
FLAG CHAR(1) NOT NULL )

What is the exactly different between these two select statements

SELECT * FROM BATCH WHERE COMPID = '1A' AND AMOUNT = '123.45'
SELECT * FROM BATCH WHERE COMPID = '1A' AND AMOUNT = 123.45

Now, my logic was that if I passed a real number as a string value ie
'123.45' ( 0x3132332E343500 char array of 7 bytes long) that the
database would be much better at accurately (since not all real numbers
can be 100% accurately represented in binary) represently the value
itself verus the client. Am I completely off base with my logic?
Yes. SQL Server has a datatype prededence, and in expressions with
different datatypes, types with lower precendence are converted to
higher (if there is an implicit conversion). '123.45' is varchar,
and AMOUNT is numeric, and numeric has higher precedence than varchar,
so the string is converted to a numeric value. Note also that 123.45
is indeed a numeric literal, and thus exact.
The reason I ask is because when I perform the first statement more
than twice in a row it says no rows found however the 2nd statement
works every time. If I close my ODBC connection and restart it and I
can repeat the statement over and over and get the correct results. Why
is this?

That's a little funny, and I don't have an answer for what is going on.
But there is no reason to put numbers into strings.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp