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'