SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 07:26 AM
| ||||
| ||||
 Syntax question 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      |
|
02-29-2008, 07:26 AM
| ||||
| ||||
| Re: Syntax question Wescotte (wescotte@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
