SQL syntax issue using "IN" keyword with dynamic data

Question

I am using SQL Server 2000 and having a wierd issue with a SQL code block (below):
A user can enter "GM" as a possible parameter or "F". If the user enters "GM" as a parameter in the stored proc query string, I need the AutoDivision to include GMC, CAD, CHE,SAT

declare @AutoDivision as varchar(15) 
set @AutoDivision = 'GM' 

if @AutoDivision = 'GM' 
            Begin 
                Select @AutoDivision = '''Cad'', ''GMC'', ''Sat'', ''Che'''
            End 

            SELECT 
                 oh.OrderNumber, lg.[lgh_number]
            FROM 
                [dbo].[OrderHeader] oh (NOLOCK)
            INNER JOIN 
                [dbo].[DistrctHeader] lg (NOLOCK) ON oh.[inv_number] = lg.[inv_number]
            INNER JOIN
                [dbo].[DealerCompany] c (NOLOCK) ON c.cmp_id = LEFT(oh.[ordernumber],3)
            INNER JOIN
                [dbo].[divisionXREF] x (NOLOCK) ON x.Division = c.comp_revtype
            WHERE
                oh.ord_number = '113-889257'
                AND x.AutoDivision IN (@AutoDivision)
                --AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')
                AND lg.[lgh_outstatus] IN ('AVAIL', 'PLAN', 'DISP', 'STRTD', 'PEND','COMP')  

However, when I run the code below, I don't get back any records. When I uncomment the code line
--AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')

it works (I get a record returned).

When I do a print 'AND x.AutoDivision IN (' + cast(@AutoDivision as varchar) + ')'
I get back AND x.AutoDivision IN ('Cad', 'GMC', 'Sat', 'Che')

What am I missing here?

Answer 1

To avoid this, you can create a temp table, fill it in, then use

IN (SELECT myField from #myTable)