Posted by Erland Sommarskog on 01/12/07 21:56

(wizofaus@hotmail.com) writes:
> Thanks...one of the most helpful replies I've had on usenet for some
> time now!
>
> The problem is that it's pretty hard for me to know that a value is
> "odd".

I can understand that this is not always simple. I didn't say this, in
hope it would be. :-)

However, I think I have a cure for you:

> In this case, like I said the query in this case is
>
> SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
>
> Where @0 is actually a value from a list that is obtained from
> elsewhere (not the database). It loops through this list, calling the
> same query for each one.

Stop! Don't do that! The problems with query plans aside, this is an
ineffecient use of SQL Server. Get all data at once with:

SELECT t.MyKey, Min(t.MyValue)
FROM MyTable t
JOIN list_to_table(@list) f ON t.MyKey = f.value
GROUP BY t.MyKey

Where list_to_table is a table-valued function that transform the list
to a table. I have a whole bunch of such functions on
http://www.sommarskog.se/arrays-in-sql.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]