Select Statement not working....

[strangeBrew12]

I have the following select statement that is not working as I expect. This will run and pull out data, but it acts as iff the not in clause does not exist.

Select *
FROM ##BatchTransaction bt
join Terminal t
on bt.TrNo = t.ATTrNo
join Agent a
on t.AgentID = a.AgentID
where a.agenttype
not in(''''+REPLACE(REPLACE('1,2,3 , A, b ,C',' ', ''), ',', ''',''')+'''')

But when I manually type in this it works as expected.
Select *
FROM ##BatchTransaction bt
join Terminal t
on bt.TrNo = t.ATTrNo
join Agent a
on t.AgentID = a.AgentID
where a.agenttype
not in('1','2','3','A','B','C')

This is actually part of a stored procedure where the 'Not In' value is a random variable that is passed from the front end application.

Hopefully someone can help me with this.

Regards

JJ

 

[SimeonJ]

I can read the query, but could you please put into words exactly what it is that you are trying to do?

 

[strangeBrew12]

Basically I am receiving a variable value (1,2,3 , A, b ,C) into the procedure. I need to remove any extra spaces and insert quote marks to make each value a string value. The above example should result as ('1','2','3','A,'B','C')

The rest of the statement will not change. I am thinking that I will need to bring in the variable, format it how I want then put it into the statement.

Thanks for your help.

JJ

 

[123FakeSt]

Well when evaluating the expression, it will become:

(' '1','2','3','A','B','C' ')

So only a match for that entire string will be excluded.

If it is always 6 then it will simply be a matter of using 6 variables. If the number varies, you may need dynamic SQL or another workaround (such as a cursor and parsing a string using CHARINDEX)



The early bird gets the worm, but the second mouse gets the cheese.