I am new to this and attempting to convert some code from VBA to a stored procedure
I need to loop and create a query with an 'in' in it.
Something like this:
This does not gripe but returns nothing
What would get this to work?
Note that I am not printing anything out immediately but need to do further processing dependin on the records I get back
In general, you're better off combining sets of data in TSQL, that's what it does. You can put all sorts of conditionals in the WHERE clause. You can also create tables out of other SELECT statements so that more complex logic can still be used as a JOIN. Looping and ad hoc/dynamic queries can lead to performance issues as well as being hard to code. Try to use the language as it's built, not force it to behave like another language.
answered Sep 10, 2011 at 04:50 AM
Grant Fritchey ♦♦
Where does the strings come from? Are they in a table, or how is the statement created?
I ask, because you say that you want your query to "loop", and that's generally not a good idea. If the data that you use to construct your IN-statement is in table(s), you could most probably create one query for it, without the need to loop.
Anyway - if you want to dynamically create an IN statement, you could use a table variable.
This is not terribly efficient and the performance could probably improve a little by using a temporary table, which you can index. But the magnitude of that performance improvement is nothing to creating one query with a set based approach.
answered Sep 08, 2011 at 09:46 PM