x

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

more ▼

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

Todd gravatar image

Todd
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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

(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))
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.
more ▼

answered Sep 08, 2011 at 09:46 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x984
x51

asked: Sep 08, 2011 at 04:10 PM

Seen: 804 times

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