Need to create an 'In' statement dynamically and return to 'recordset'
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: set @s = 'DN','KT','WT' print @s; SELECT sales_amt from sales where department in( @s) ; 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 Thanks JPS
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. DECLARE @t table (s char(2)) INSERT INTO @t (s) SELECT 'DN' UNION ALL SELECT 'KT' UNION ALL SELECT 'WT' SELECT sales_amt FROM sales WHERE department IN (SELECT s FROM @t) 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.
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.