IN Function

  • Is there a way to use a local variable as the expression part of the IN statement. I have an example of what I'm trying to do below.

    CREATE TABLE [CARRIER] (

    [CARRIER_CODE] [int] NULL ,

    [CARRIER] [varchar] (50) NULL

    ) ON [PRIMARY]

    GO

    insert carrier values ( 1,'ATT')

    go

    insert carrier values ( 1,'UPS')

    go

    insert carrier values ( 1,'AIRBN')

    go

    insert carrier values ( 1,'FEDEX')

    go

    insert carrier values ( 1,'SNET')

    go

    CREATE PROCEDURE PROC1

    AS

    BEGIN

    SET QUOTED_IDENTIFIER Off

    go

    DECLARE @a varCHAR(200)

    SET @a = "'AIRBN'" + ',' + "'UPS'" + ',' + "'FEDEX'"

    PRINT @a

    select *

    from carrier

    where CARRIER in (@A)

  • Two easy ways:

    (1) Dynamic SQL - SET @sqlCmd = 'select *

    from carrier

    where CARRIER in ( ' + @a + ')'

    sp_executesql @sqlCmd

    (2) Temporary table

    CREATE TABLE #tmpLookup (

    CARRIER VARCHAR(5) )

    INSERT INTO #tmpLookup VALUES ( 'AIRBN' )

    INSERT INTO #tmpLookup VALUES ( 'UPS' )

    INSERT INTO #tmpLookup VALUES ( 'FEDEX' )

    SELECT * FROM [CARRIER] C

    INNER JOIN #tmpLookup T ON T.CARRIER = C.CARRIER

    Guarddata-

  • Another easy way is to use a function to split your string.

    I have a functin called SplitString which requires a string and a delimiter. It returns a table.

    Then use:

     
    
    Select * From carrier where Carrier in (Select value from dbo.SplitString(@A, ',')

    or

    
    
    Select C.* From Carrier C JOIN dbo.SplitString(@A, ',') T ON C.Carrier = T.Carrier

    The latter version has given me some performance issues in the past.

    Could also use the function to insert into a normal temp table and join that.

    
    







    CREATE FUNCTION SplitString(
    @InputVarchar(1000),
    @DelimiterVarchar(3)
    )
    RETURNS @vTempStringsTable (ValueVarchar(100))
    AS
    BEGIN
    Declare
    @vtmpString Varchar(100),
    @vMarkInt,
    @vLengthInt


    If SubString(@Input, DataLength(@Input), 1) <> @Delimiter
    Begin
    Set @Input = @Input + @Delimiter
    End

    SET @vLength = Len(@Input)
    While (@vLength > 1)
    BEGIN
    SET @vMark = PatIndex('%' + @Delimiter + '%', @Input)
    IF @vMark <> 0
    BEGIN
    INSERT INTO @vTempStrings
    Values(SubString(@Input, 1, @vMark - 1))
    SET @Input = SubString(@Input, @vMark + 1 ,Len(@Input))
    END
    SET @vLength = @vLength -1
    END
    Return
    End

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • Or why not use the CHARINDEX function instead of IN, this would avoid creating a temp table and avoid doing any parsing:

    SELECT *

    FROM carrier

    WHERE CHARINDEX("'" + CARRIER + "'", @a) > 0

    Cheers

    barwickl

  • Using CHARINDEX can cause major problems with false positives...

    If you have a value in the db of 1 and your are check using CHARINDEX against a list of say 10,11,12,13 then you will receive a false positive. It will say that 1 exists in the list.

    One way around this is to pad both side of the query. So instead checking the db value of 1 your check somthing like 0001 against a list of 0010,0011,0012,0013. I have had to do this before due to certain restraints and have created a UDF to do this 'padding' for me.

    Dan

  • Not sure about the false positives. The way I read :

    SELECT *

    FROM carrier

    WHERE CHARINDEX("'" + CARRIER + "'", @a) > 0

    the single quotes (to the left and the right of column CARRIER) should take care of that...

    CVM.

  • I usually basically do what Crispin does in his example. I feel this is much easier to read and I already have the function to split the string 🙂

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 7 posts - 1 through 6 (of 6 total)

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