Thread: Query help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Unhappy Query help


    Hello Everyone,

    I have a query that looks something like this:

    DEFINE @VAR_A VARCHAR(6)

    DECLARE trsite_cursor CURSOR FOR
    SELECT DISTINCT AppField
    FROM TABLE_1
    ORDER BY 1

    OPEN trsite_cursor

    FETCH NEXT FROM trsite_cursor INTO @VAR_A

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SELCMD ='SELECT * FROM TABLE_2
    WHERE Field1 =' + @VAR_A +
    ' ORDER BY 1,2,3;'
    END
    ..
    ..
    ..
    EXEC xp_sendmail @query = @SELCMD,
    ..
    ..

    and the rest of the query......

    The column "AppField" in TABLE_1 has been defined as varchar. Let's assume it contains the value: ABCD. When I run it, the query fails at the SET @SELCMD statement, saying that the column name ABCD is invalid. It assumes that ABCD is a column name & not a value. However, if AppField contains a numeric value, ex: 123, I don't get any errors & the query outputs the desired results.

    So, I guess, the question is: how do I make the SET @SELCMD treat the value in AppField as "ABCD" or 'ABCD' and not just ABCD?

    Thanks,
    Suhas
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    SET @SELCMD ='SELECT * FROM TABLE_2
    WHERE cast(Field1 as varchar(10)) =''' + @VAR_A +
    ''' ORDER BY 1,2,3;'
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Thumbs up


    Thanks swampBoogie. Works perfect!

IMN logo majestic logo threadwatch logo seochat tools logo