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



more ▼

asked Sep 08, 2011 at 04:10 PM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Sep 10, 2011 at 04:50 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

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))
 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.

more ▼

answered Sep 08, 2011 at 09:46 PM

avatar image

Magnus Ahlkvist
22.5k 20 43 43

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:


(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

Sep 09, 2011 at 03:53 PM Todd
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 08, 2011 at 04:10 PM

Seen: 1076 times

Last Updated: Sep 08, 2011 at 09:39 PM

Copyright 2018 Redgate Software. Privacy Policy