SQL - WHERE clause with variable value

[beecool]

Hi,
I don't know how to use WHERE with a variable instead of a constant. For
EX, how do write "SELECT * FROM table WHERE age>=inputAge" where inputAge
is a variable that will take a value later on during execution instead of
at design time.
Please help if you can
Thanks a lot.

[KevinV]

At least one way is to put the select statement in a stored procedure. The
stored procedure should take one parameter. The parameter would be the argument
on the where clause.

"beecool" <thoaingan@yahoo.com> wrote:
>
>Hi,
>I don't know how to use WHERE with a variable instead of a constant. For
>EX, how do write "SELECT * FROM table WHERE age>=inputAge" where inputAge
>is a variable that will take a value later on during execution instead of
>at design time.
>Please help if you can
>Thanks a lot.

[Ken]

"beecool" <thoaingan@yahoo.com> wrote:
>
>Hi,
>I don't know how to use WHERE with a variable instead of a constant. For
>EX, how do write "SELECT * FROM table WHERE age>=inputAge" where inputAge
>is a variable that will take a value later on during execution instead of
>at design time.
>Please help if you can
>Thanks a lot.

If inputAge is declaired as a string use this
"SELECT * FROM table WHERE age>= '" & inputAge & "'"

If inputAge is declaired as an integer or other numeric type use this
"SELECT * FROM table WHERE age>= " & inputAge

[Regmo]

Take care, beecool... Assuming you plan to issue this SQL statement from within
VBA/VB6.0 source code, Ken's response contains a common misunderstanding
about dynamic SQL.

The datatype of the “inputAge” variable DOES NOT determine the use of quotes
around the right-side value in a SQL expression; rather the datatype of the
age column in the table does.

It is likely an age column would be defined in the table DDL (data definition
language, i.e., CREATE TABLE statement) as int, real, float, or double (depending
on the SQL database product), so the SQL query processor expects to compare
the age column’s value to either a numeric literal (i.e. a number without
quotes) or a value from a column with the same or compatible datatype. Thus,
this SQL statement must end up as follows (assuming inputAge = 39):

SELECT * FROM yourtable WHERE age >= 39

Your programming concern is to ensure the SQL statement is formatted as a
text string that is a valid SQL statement to the SQL query processor. VBA/VB6
takes care of converting variables according the context in which they are
referenced, so when variables appear in a string expression, they are automatically
converted to strings. Thus, the following statement will produce a syntactically
correct SQL statement, regardless of the datatype of the inputAge variable:

Dim strSQL as String
Dim inputAge as Long
strSQL = “SELECT * FROM yourtable WHERE age >= ” & inputAge

Possible exceptions are Date/Time and Boolean values, which may be handled
differently by different SQL databases and/or stored procedures. For example,
by default the Jet 3.x and 4.x Engines in MS Access expects date literals
to be “mm/dd/yy” delimited by number signs (#), so the above line becomes:

Dim inputBirthdate as Variant
strSQL = “SELECT * FROM yourtable WHERE birthdate >= #” & Format(inputBirthdate,
“Short Date”) & “#”

SQL Server 6.5 stored procedures expect the date May 28, 2001 to appear as
the string “28-MAY-01” in SQL expressions. Thus, the assignment to strSQL
would change to the following (note the single quotes preceding and trailing
the formatted inputBirthdate value):

strSQL = “SELECT * FROM yourtable WHERE birthdate >= ’” & Format(inputBirthdate,
“yy-MMM-dd”) & “’”

If yout SQL statement is inside a SQL Server stored procedure, then there
is no need to concatenate the statement together; just type it in and reference
the variable as a passed parameter:

CREATE PROCEDURE GetPendingBirthdays ( @inputBirthdate DATETIME )
AS
SELECT * FROM yourtable WHERE birthdate >= @inputBirthdate

Except for the at-sign (@) sign, which is required to indicate a varaible
is local to the stored procedure, this solution look just the way you first
imagined it would! Just type a call to the stored procedure from VB code,
pass in the input variable, and you good to go.

Hope this helps,
Regmo




"Ken" <enterprise.@127.0.0.1> wrote:
>
>"beecool" <thoaingan@yahoo.com> wrote:
>>
>>Hi,
>>I don't know how to use WHERE with a variable instead of a constant. For
>>EX, how do write "SELECT * FROM table WHERE age>=inputAge" where inputAge
>>is a variable that will take a value later on during execution instead
of
>>at design time.
>>Please help if you can
>>Thanks a lot.
>
>If inputAge is declaired as a string use this
>"SELECT * FROM table WHERE age>= '" & inputAge & "'"
>
>If inputAge is declaired as an integer or other numeric type use this
>"SELECT * FROM table WHERE age>= " & inputAge

Hi

The way of passing input depends on the way you are invoking the query. If
it is through SQL directly then put a "&" like WHERE age>=&inputAge. I think
it will prompt you for parameter. If it via a programming language "application
programming" then populate host variable accordingly. Hope this helps.

[nodierl]

i'm having the same problem using VB 2008 Express Edition. I'm trying to set up a query using a local variable but I don't know how to write it in the query itself
these is what a have:
Dim fechai As Date, fechaf As Date
.
.
.
fechai = Datei.Value
fechaf = Datef.Value

and here is the query:
SELECT Id, Fecha, [Cantidad de Grupo], [Tipo de Visita], Idioma FROM Visitas
WHERE Fecha>=fechai.to_date AND Fecha <=fechaf.to_date

but when I run the application it returns the exception:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

How can I fix that?

[Hack]

Welcome to DevX

What is: fechai?

[nodierl]

fechai and fechaf are date variables as they are shown on in
Dim fechai as Date
Dim fechaf as Date

(sorry for the names, I'm using spanish words to identify them)

[Emefa]

Nodierl,

Check to see if this applies to you.
http://support.microsoft.com/kb/317161

[nodierl]

hmm actually that does not apply to my case

[Hack]

Try

SELECT Id, Fecha, [Cantidad de Grupo], [Tipo de Visita], Idioma FROM Visitas
WHERE Fecha>='" & fechai.to_date & "' AND '" & Fecha <= '" & fechaf.to_date & "' "