September 24, 2004 at 10:18 am
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)',
@varCOAAcctNumberInclude VARCHAR(500)' ,
@intJournalPostedFlag = 1,
@varCOAAcctNumberInclude = '11, 85'
September 27, 2004 at 8:00 am
This was removed by the editor as SPAM
September 27, 2004 at 8:52 am
Hey,
Are we talking about numerical data or text? For an insert statement, you can do:
fieldname in ('10', '85', '13')
'10, 85' wouldn't work because that's assuming one value.
For numerical, remove the single quotes:
fieldname in (10, 85)
Brian
September 27, 2004 at 8:57 am
He posted this question in triplicate or more. I gave basically the same response which was at the following post:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=138739#bm138752
September 27, 2004 at 9:11 am
Hey,
Yeah, the one thing that I should of mentioned but didn't, and that is present in Aaron's response is that for string values, you need to use '' to represent a single ' around each value, such as:
''11'', ''85''
Brian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply