Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    2

    Unanswered: IN Operator problem

    Hello,

    I have to pass a string list separated by Commas ( For example, 'Item1','Item2') from a C# code to run a Stored procedure which has a Select query which has an IN Operator in it.

    While passing though the data are there in the database, Stored procedure doesn't recognise the passed string list as a list instead it recognise as a whole string, so query doesn't return the expected resultset.

    Please help me on this how to pass a stringlist to a stored procedure from C#?

    Thanks,
    Muthu

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    post the stored procedure.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2005
    Posts
    2
    Thanks for your response,

    Here is the stored procedure

    CREATE PROCEDURE sp_Test(@machinename varchar(30) ) AS
    SELECT MachineName FROM MachineTable WHERE MachineName IN(@machinename)

    Regards,
    Muthu

  4. #4
    Join Date
    Oct 2003
    Posts
    84
    Maybe

    EXEC('SELECT MachineName FROM MachineTable WHERE MachineName IN ' + @MachineName);

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't forget the parentheses that need to go around the list

    EXEC( 'SELECT ... WHERE MachineName IN ( ' + @MachineName + ' ) ' );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Take a look at the UDF Jeff Smith posted
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I was just going to reccomed do something like concatenating you sql string in your application like so

    EXEC getTheTestMe @sqlstr = 'SELECT [ID],Letter FROM DatabaseName.dbo.TESTME WHERE Letter in (''A'',''B'',''C'')'

    and then passing it to the old stored procedure like this.

    ALTER PROC getTheTestMe
    @sqlstr nvarchar(100)
    AS
    execute master.dbo.sp_executesql @sqlstr
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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