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?

OMG Ponies · Accepted Answer · 2010-12-15 21:39:29Z

You can't use a single variable to represent a comma separated list of IN parameters in SQL - Oracle, MySQL, SQL Server... doesn't matter.

To get this variable method to work, you need to use dynamic SQL so you are able to create the query as a string first (using concatenation to get the IN parameters from your variable), and the query statement is executed afterwards:

DECLARE @cmd VARCHAR(1000)

SET @cmd = 'SELECT oh.OrderNumber, 
                   lg.[lgh_number]
              FROM [dbo].[OrderHeader] oh (NOLOCK)
              JOIN [dbo].[DistrctHeader] lg (NOLOCK) ON oh.[inv_number] = lg.[inv_number]
              JOIN [dbo].[DealerCompany] c (NOLOCK) ON c.cmp_id = LEFT(oh.[ordernumber],3)
              JOIN [dbo].[divisionXREF] x (NOLOCK) ON x.Division = c.comp_revtype
             WHERE oh.ord_number = '113-889257'
               AND x.AutoDivision IN ('+ @AutoDivision +')
               AND lg.[lgh_outstatus] IN (''AVL'', ''PLN'', ''DSP'', ''STD'', ''PND'',''CMP'')  '

EXEC(@cmd)

I recommend reading The Curse and Blessings of Dynamic SQL before implementing a dynamic SQL solution.

Table Valued Function

A table valued function would allow you do what you want without using dynamic SQL -- there's more info in this article.

Mark SQLDev · Accepted Answer · 2010-12-15 21:28:21Z

5

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

IN (SELECT myField from #myTable)

answered Dec 15, 2010 at 21:28

Mark SQLDev

5393 silver badges6 bronze badges

2 Comments

user279521 Over a year ago

your solution was simple and easy to maintain. Thanks!!

Mark SQLDev Over a year ago

You're welcome, that's what I aim for. :0) By the way, I have a handy proc that converts a string of choices like that into a table automatically. If you find yourself doing this often, you could look it up.

Paul Sasik · Accepted Answer · 2010-12-15 21:59:47Z

3

Even though you concatenate what looks like a few different arguments your IN clause is actually testing it as a single string (you did declare it as a varchar) and it's no surprise that no records match that predicate.

Looks like you're trying to mix dynamic sql and a standard query. That won't work. Your query either has to be all dynamically created and then specially executed or your IN clause has to be inputed with individual arguments which can be done by subqery.

You could do something like:

... AND x.AutoDivision IN 
(SELECT Division WHERE Corp = 'GM') ...

OR

... AND x.AutoDivision IN 
(SELECT 'Cad' UNION SELECT 'GMC' UNION SELECT 'Sat' UNION SELECT 'Che') ...

Try this:

        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 
           (SELECT 'Cad' UNION SELECT 'GMC' UNION SELECT 'Sat' UNION SELECT 'Che')
            AND lg.[lgh_outstatus] IN ('AVL', 'PLN', 'DSP', 'STD', 'PND','CMP')

That is an inline subquery that UNIONs arbitrary strings into a result set. (Take with a grain of salt. I am a long way from a Sql Server interface.)

edited Dec 15, 2010 at 21:59

answered Dec 15, 2010 at 21:28

Paul Sasik

81.9k23 gold badges152 silver badges194 bronze badges

3 Comments

user279521 Over a year ago

Paul, just so you know, a user can enter "GM" or "F" (updated my original post). If they enter "F", everything is fine. If they enter "GM", thats where the complexity begins.

Paul Sasik Over a year ago

Because GM has divisions and Ford doesn't? OK. Try that. (Replaced the string with a UNION set of subqueries)

user279521 Over a year ago

Well, technically, because GM needs to show a detailed report... but thats business logic

Bnjmn · Accepted Answer · 2010-12-15 21:28:05Z

1

   AND x.AutoDivision IN (@AutoDivision)
        --AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')

Can you clarify these two lines of code, are they not doing the same thing?

answered Dec 15, 2010 at 21:28

Bnjmn

2,0091 gold badge21 silver badges34 bronze badges

2 Comments

OMG Ponies Over a year ago

The former is not supported by SQL; the latter (commented out) is supported.

user279521 Over a year ago

They are supposed to be the same, but the top line does not return a record, the bottom line returns a record.

Netloh · Accepted Answer · 2014-09-16 20:50:45Z

0

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

DECLARE @AutoDivision varchar(50) = NULL
SET @AutoDivision ='Cad, Sat, GMC, Che'

SELECT * FROM divisionXREF P INNER JOIN dbo.SPLIT(ISNULL(@AutoDivision,''),',') as T ON P.lgh_outstatus = CASE T.strval WHEN '' THEN P.PG_CODE ELSE T.strval END

edited Sep 16, 2014 at 20:50

Netloh

4,3784 gold badges27 silver badges38 bronze badges

answered Sep 16, 2014 at 20:30

Rajesh Manjarekar

1

1 Comment

Jose Magana Over a year ago

You need to format this and explain what you're doing to provide a good answer.

Collectives™ on Stack Overflow

SQL syntax issue using "IN" keyword with dynamic data

5 Answers 5

Table Valued Function

Comments

2 Comments

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Table Valued Function

Comments

2 Comments

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related