'IN' problem

  • toco

    Grasshopper

    Points: 20

    I have a SP, which has a character input variable.

    I want to use this variable as the IN expression for an integer field.

    Example:

    @Variable ='1,2,3,4'

    CREATE PROCEDURE SP_Name

    (@Variable varchar(255))

    AS

    Select * from table where field in (@Variable)

    Like this i will get an error :

    Syntax error converting the varchar value '1,2,3,4' to a column of data type int.

    Edited by - toco on 06/03/2002 02:44:55 AM

  • Antares686

    SSC Guru

    Points: 125444

    Your field must be an int column which it is trying to match the case. However, the value when submitted via a single variable like this is treated as a single string not multiple items. Try this should help you get thru it, there are other ways but this works best for me.

    @Variable ='1,2,3,4'

    CREATE PROCEDURE SP_Name

    (@Variable varchar(255))

    AS

    SET NOCOUNT ON

    CREATE TABLE #tmpValues (

    [val] [int] NOT NULL

    )

    WHILE CHARINDEX(',',@Variable) > 0 --Look for a , in string

    BEGIN

    INSERT INTO #tmpValues (val) VALUES (CAST(LEFT(@Variable, CHARINDEX(',',@Variable) - 1) AS INT)) --Insure or datatype

    SET @Variable = RIGHT(@Variable,LEN(@Variable) - CHARINDEX(',',@Variable))

    END

    INSERT INTO #tmpValues (val) VALUES (CAST(@Variable AS INT))

    Select * from table where field in (SELECT val FROM #tmpValues)

    DROP TABLE #tmpValues

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • toco

    Grasshopper

    Points: 20

    Thanks Lena

Viewing 3 posts - 1 through 3 (of 3 total)

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