CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000subquery
user279521
user279521

Reputation: 4807

SQL syntax issue using "IN" keyword with dynamic data

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?

Upvotes: 3

Views: 2017

Answers (5)

Rajesh Manjarekar
Rajesh Manjarekar

Reputation: 1

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

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81547

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.)

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332731

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.

Upvotes: 10

Mark SQLDev
Mark SQLDev

Reputation: 539

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

IN (SELECT myField from #myTable)

Upvotes: 5

Bnjmn
Bnjmn

Reputation: 2009

   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?

Upvotes: 1

Related Questions