SQL - WHERE clause with variable value


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: SQL - WHERE clause with variable value

  1. #1
    beecool Guest

    SQL - WHERE clause with variable value


    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.

  2. #2
    KevinV Guest

    Re: SQL - WHERE clause with variable value


    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.



  3. #3
    Ken Guest

    Re: SQL - WHERE clause with variable value


    "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

  4. #4
    Regmo Guest

    Re: SQL - WHERE clause with variable value


    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



  5. #5
    Guest

    Re: SQL - WHERE clause with variable value


    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.

  6. #6
    Join Date
    Jan 2010
    Posts
    3
    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?

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    What is: fechai?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Jan 2010
    Posts
    3
    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)

  9. #9
    Join Date
    Jul 2007
    Location
    Minnesota
    Posts
    155
    Nodierl,

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

  10. #10
    Join Date
    Jan 2010
    Posts
    3
    hmm actually that does not apply to my case

  11. #11
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try
    Code:
    SELECT Id, Fecha, [Cantidad de Grupo], [Tipo de Visita], Idioma FROM Visitas
    WHERE Fecha>='" & fechai.to_date & "' AND '" & Fecha <= '" & fechaf.to_date & "' "
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center