Mutliple Values in a Variable

  • We have 2 types of customers. Automotive and Industrial. Automotive is compromised of Customer Classes MD, RD and RS. Industrial is compromised of Customer Classes ID, IW and JP. I am trying to set up a report where the user will pick Auto, Industrial or All. My problem is that when I set up the variable declarations, I cannot remember how to set up multiple values in a SET statement. Depending on the selection of the user, these variables will be pass down to a WHERE IN statement. I've tried different syntaxes, but I just cannot remember how this is done. Here is the gist of the declaration.

    DECLARE @auto varchar(20)

    DECLARE @industrial varchar(20)

    SET @auto='MD','RD','RS'

    SET @industrial='ID','IW','JP'

    When the above is executed, I'll get a syntax error with the first comma. I've also tried SET @auto='MD'+','+'RD'+','RS' , but I'll get 0 results when used in the WHERE CustomerClass IN (@auto)

    Any ideas?

  • How about this: SET @auto='MD,RD,RS'

  • You will have to use Dynamic SQL.

  • dsdeming (10/8/2009)


    How about this: SET @auto='MD,RD,RS'

    I've tried that, but what it does is treat everything inside the ' ' as 1 value of MD,RD,RS instead of the 3 separate ones. In the IN statement, I have to have each value inside the single quote, i.e. 'MD','RD','RS'

  • I wound up using a couple of IF Else statements.

    IF @type='Auto'

    BEGIN

    Select *

    From Table

    Where Class in ('RD','MD','RS')

    END

    IF @type='Industrial'

    BEGIN

    Select *

    From Table

    Where Class in ('ID','IW','JP')

    END

    ELSE

    BEGIN

    Select *

    From Table

    END

  • adams.squared (10/8/2009)


    I wound up using a couple of IF Else statements.

    IF @type='Auto'

    BEGIN

    Select *

    From Table

    Where Class in ('RD','MD','RS')

    END

    IF @type='Industrial'

    BEGIN

    Select *

    From Table

    Where Class in ('ID','IW','JP')

    END

    ELSE

    BEGIN

    Select *

    From Table

    END

    Well there's always something like this.

    CREATE TABLE @classes (classcode char(2),classtype varchar(20))

    INSERT INTO @classes

    SELECT

    'RD','Auto'

    UNION ALL

    SELECT

    'MD','Auto'

    UNION ALL

    SELECT

    'RS','Auto'

    UNION ALL

    SELECT

    'ID','Industrial'

    UNION ALL

    SELECT

    'IW','Industrial'

    UNION ALL

    SELECT

    'JP','Industrial'

    SELECT

    a.*

    FROM

    table a JOIN @classes b ON

    a.Class = b.classcode

    WHERE

    b.classtype = @type



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • One more solution in Single query.

    Select * from table_1 where [Class] in

    (case when @type='Auto' then 'MD' else 'ID' end,

    case when @type='Auto' then 'RD' else 'IW' end,

    case when @type='Auto' then 'RS' else 'JP' end

    )

    but for performace your solution of 'IF ELSE' itself would be better it seems as it is less complex

  • Regarding Mark's solution, what would (IMHO) be better is if your application already had this table defined. Those codes are coming from someplace, just connect the ClassCode to the ClassType in this table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Use Dynamic query...

    DECLARE @auto varchar(20)

    DECLARE @industrial varchar(20),@SQL varchar(500)

    SET @auto='MD'','+'''RD'

    SET @industrial='ID'','+'''IW'

    Set @sql='select * from Customer where CustomerClass in ('''+@ind+''')'

    Exec (@SQL)

  • Listen to Wayne.

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • WayneS (10/9/2009)


    Regarding Mark's solution, what would (IMHO) be better is if your application already had this table defined. Those codes are coming from someplace, just connect the ClassCode to the ClassType in this table.

    I completely agree... I was surprised that such a table wasn't mentioned...

    But I'm also getting used to the stuff I have around here... and there is the twisted possibility that the table with those codes related to types does not exist. I know here there are several code type fields that have no relating table that categorizes the codes (or the table was dropped, and nobody ever declared a foreign key).



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • The MD, RD, ID, etc are coming from the Customer Table. They are the Class of Trade codes that we use. There isn't a table that says that MD is Automotive, so I am left with defining what belongs where within the script. I could always create another table, use up one of the User Defined fields in the application table, or use the temp table method as suggested. There are lots of ways to skin this cat. If the request for this becomes more common in the future, then I will probably use one of the user defined fields. Otherwise, I'll just leave it as a script.

    Thanks for all of the responses.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply