Using sp_ExecuteSQL

• Craig Berko

  SSC Rookie

  Points: 45

  More actions

  September 24, 2004 at 10:17 am

  #66920

   

  I'm trying to use the system stored procedure sp_SQLExecuteSQL to run dynamic SQL within an application.  However one of the WHERE clauses on my SQL statement uses an IN clause.  The system stored procedure will not run the SQL correctly when more than one value exists in the IN clause.  An example of how the code looks is below.  I've changed the IN Clause from '''11'', ''85''' to '11, 85' and neither has returned any results.  When I run the statement straight up it returns results.

   

  sp_ExecuteSQL

  N'SELECT tGLJournalItem.JItemDebitAmount, tGLJournalItem.JItemCreditAmount

  FROM tGLJournal WITH (nolock) 

     INNER JOIN tGLJournalItem WITH (nolock) ON tGLJournal.JournalKey =  tGLJournalItem.JItemJournalID 

     INNER JOIN tChartOfAccounts WITH (nolock) ON tGLJournalItem.JItemGLAccountID = tChartOfAccounts.COAKey

  WHERE tGLJournal.JournalPostedFlag = @intJournalPostedFlag 

    AND LEFT(RIGHT(tChartOfAccounts.COAAcctNumber, 5), 2) IN (@varCOAAcctNumberInclude)',

  N'@intJournalPostedFlag INT,

  @varCOAAcctNumberInclude VARCHAR(500)' ,

  @intJournalPostedFlag = 1,

  @varCOAAcctNumberInclude = '11, 85'

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  September 24, 2004 at 11:12 am

  #524380

  You told it to look for a single variable of eleven comma space eighty five. You want it to look for the value eleven or the value eighty five. Therefore, you need to split it into two values or else concat the values literally into the dynamic SQL.

  Two values:

  .....

    AND LEFT(RIGHT(tChartOfAccounts.COAAcctNumber, 5), 2) IN (@varCOAAcctNumberInclude1, @varCOAAcctNumberInclude2)',

  .....

  @varCOAAcctNumberInclude1 = '11'

  @varCOAAcctNumberInclude2 = '85'

  Dynamic:

  set locVar1 = '11'

  set locVar2 = '85'

  ...

    AND LEFT(RIGHT(tChartOfAccounts.COAAcctNumber, 5), 2) IN (''' + @locVar1 + ''', ''' + @locVar2 + ''')',