November 20th, 2003, 03:02 PM
-
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
November 20th, 2003, 03:07 PM
-
Code:
SET @SELCMD ='SELECT * FROM TABLE_2
WHERE cast(Field1 as varchar(10)) =''' + @VAR_A +
''' ORDER BY 1,2,3;'
November 20th, 2003, 03:40 PM
-
Thanks swampBoogie. Works perfect!