Posted by Erland Sommarskog on 12/05/05 02:09

OK, I read your post in a little more detail, and it appears that running
dynamic cursor over the report selections table is about inevitable. At
least it becomes very complex to build an entirely static solution.
And since supposedly the number of selections is small (< 10), the
cursor will not come with any performance penalty.

If you have an OR question, it simple. You combine all the SELECTs with
UNION, and then you have your result.

For AND there is the INTERSECT operator in SQL 2005, but I assume that
you are on SQL 2005. You could still use UNION, but you would augment
the query with the an id identitying the query. The AUTOID in the
REP_SEL_DET table would do. Say that you get the result into a temp
table. Then you can do:

SELECT EmployeeID
FROM #temp
GROUP BY EmployeeID
HAVING COUNT(*) = (SELECT COUNT(*) FROM REP_SEL_DET WHERE
REPSELNO = @REPSELNO)

Then again, your idea of running the queries on by one, and getting
off when you have an empty set is not bad, and gives some optimization.

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