Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Boca Raton, FL
    Posts
    5

    Question Unanswered: Using IN with a varchar string of int's?

    I am passing in a string of numbers as a varchar (ie '1,2,3,4,5') to a stored procedure. If i create an EXEC statement it works fine, it wont give me an error because it is working with the varchar as a list of int's. How can i do this w/out using an EXEC statement? Using an EXEC statement i loose security and i have gi give permissions on tables, of which i dont want to do. Is there another way to do this using standard stored procedures w/out the EXEC command. Thanks for your time.

    Code:
    DECLARE @VAR
    SET @VAR = '1,2,3,4,5'
    
    EXEC('SELECT * FROM Employees WHERE EmployeeID IN('+@VAR+')')

  2. #2
    Join Date
    May 2003
    Location
    Amsterdam, the Netherlands
    Posts
    4

    sp_executesql

    Have you tried to use: sp_executesql ?

  3. #3
    Join Date
    May 2002
    Location
    Boca Raton, FL
    Posts
    5
    I still need to give select permissions to the tables, which is what i am trying to prevent.

  4. #4
    Join Date
    Jul 2002
    Posts
    63

    Talking

    To soultion is to convert the string to recordset and then you can use regular IN operator on the Function recordet result .
    The solutrion allow you to use precomple store procedure !! .




    --Eyal Create SP
    CREATE FUNCTION SQL_FN_SeparateStringToRecordSet
    ( @szString Varchar(1000),
    @szDelimiter char )

    RETURNS @TempporaryTable TABLE
    ( c1 int )
    AS
    BEGIN

    DECLARE @szCurrentValue AS varchar(1000)
    DECLARE @nlen AS int

    WHILE (2>1)
    BEGIN
    IF ( CHARINDEX(@szDelimiter,@szString) = 0 )
    BEGIN
    IF LEN(@szString)>0
    BEGIN
    --Insert the First / Last Vlaue
    INSERT INTO @TempporaryTable VALUES (@szString)
    END
    BREAK -- Out of while end of string
    END

    SET @nlen = ( CHARINDEX(@szDelimiter,@szString) -1 )
    SET @szCurrentValue = SUBSTRING(@szString,1,@nlen)

    INSERT INTO @TempporaryTable VALUES (@szCurrentValue)
    SET @szString = SUBSTRING(@szString , CHARINDEX(@szDelimiter,@szString)+ 1 , len(@szString))


    END --End while
    Return
    END --End fun


    --Eyal !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •