sql strings

x = 1 ' integer
y = "1" ' string


which one(s) are correct?

sql = "SELECT * "
sql = sql & "FROM SomeTable "
sql = sql & "WHERE SomeField = ' " & x & " '

or

sql = "SELECT * "
sql = sql & "FROM SomeTable "
sql = sql & "WHERE SomeField = " & x & "

or

sql = "SELECT * "
sql = sql & "FROM SomeTable "
sql = sql & "WHERE SomeField = ' " & y & " '

or

sql = "SELECT * "
sql = sql & "FROM SomeTable "
sql = sql & "WHERE SomeField = " & y & "



i am so confused


i am working with ASP

I don't know anything about ASP, but - generally speaking - numbers should be left alone (with no quotes) while strings should be enclosed in quotes.

In Oracle, you'd do something like this:

SELECT * FROM some_table
WHERE number_column = 1
AND char_column = '1'

Sometimes it might work OK if you leave it this way:
... AND char_column = 1
because DB engine would perform implicit conversion from a number to a character. But this is not recommended as you might end with a VALUE ERROR (for example, you have numbers stored into the character column for years, and then, suddenly, someone enters 'A' into that column which is perfectly OK regarding the database, but your code will fail if there are no enclosing single quotes on the variable).

Therefore, your 2nd and 3rd answer seem to be correct from my point of view (which doesn't have to be correct).

Remember, in classic ASP, all variables are considered "variant", so when it comes to SQL, it does not matter if X = 1 or Y = "1"

However, this *is* important in the ASP code (but not SQL code)
However, When you are sending a select statement to SQL SERVER, it *all* gets sent as a string.

Asssume the following table, PERSON, in Sql Server.
AGE is INT
NAME is char(50)

In ASP, you can do:
Notice that there are *no* quotes around X or Y. Quotes imply that it is a string. AGE is an INT, so it expects an int. In both cases, SQL will intepret this as
Select * from PERSON, Where AGE = 1


This is also allowed:
Notice that I *do* have single quotes around X and Y. Becuase NAME is a char() and expects a string. In both cases, SQL will intepret this as
Select * from PERSON, Where NAME= '1'

Please also note that in SQL Server, strings must be in single quotes.


Last Example:
In example 1, AGE is expecting an INT, so by sending it the string '1', it causes an error.

In example 2, NAME is expecting a CHAR, so by send it the numeric 1, it causes an error.


~Le