Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: variable in a field

    Could anyone tell me why this isn't working, or if it simply can't be done.

    DECLARE @x varchar
    SET @x = 'ITEMNMBR'

    SELECT *
    FROM IV00101
    WHERE @x between '3530001' and '3530020'

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WHERE @x between '3530001' and '3530020'
    ...translates literally to...
    WHERE 'ITEMNMBR' between '3530001' and '3530020'
    ...which is of course always false, and so no rows are returned. It's just performing a string comparison, and I suspect that you are trying to get it to dynamically compare to a specific column called ITEMNMBR.

    There are other ways to skin this cat. If you know the column names ahead of time then one method is the following:

    where (@X = 'ITEMNMBR' and ITEMNMBR between '3530001' and '3530020')
    or (@X = 'COLUMN2' and COLUMN2 between '3530001' and '3530020')
    or (@X = 'COLUMN3' and COLUMN3 between '3530001' and '3530020')
    ...etc...

    If you want a completely dynamic solution where you don't have to hard-code the column names, then it gets much more complicated, especially since you will need to check and account for errors where the specified column name does not exist. If this is the case, I suggest you rethink your application design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    There's an option in my application where you can do a search with the product code (ITEMNMBR) or the product name (ITEMDESC) and I just wanted to send a parameter of the column name to the select to make my life easier...

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by diegocro
    There's an option in my application where you can do a search with the product code (ITEMNMBR) or the product name (ITEMDESC) and I just wanted to send a parameter of the column name to the select to make my life easier...
    blindman's solution will work. The other option is dynamic SQL:

    execute sp_executesql 'SELECT *'
    + 'FROM IV00101'
    + 'WHERE ' + @x + ' between ''3530001'' and ''3530020'''

  5. #5
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Quote Originally Posted by RogerWilco
    blindman's solution will work. The other option is dynamic SQL:

    execute sp_executesql 'SELECT *'
    + 'FROM IV00101'
    + 'WHERE ' + @x + ' between ''3530001'' and ''3530020'''
    this will help, thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •