asked
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.
The list of depts comes from another table. There is a lot of conditional logic that needs to be performed on whatever comes back in the row depending on whether there were LY sales or if we have a current plan for a department or the division that sold it and a bunch of other crap. We have to calculate this: LY DEPT DAY/LY DEPT MONTH)/(LY TOTAL DAY/LY TOTAL MONTH))*TOTAL COMPANY CONTRIBUTION (Total company contribution comes from another table) in a loop for each day of the month for each department in each division. Also, depts can span divisions, since we have outlet stores There are loops inside of loops. Get division get depts get days of the month calculate a daily contribution if there are no ly sales do one thing if there are ly sales do something else if the users want to do something else for specific depts, do something else So, a real pain
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.
