Here is an idea: - Create a new table PickOrder with 2 columns - division and chosenName. - Iniitialize PickOrder with 4 records -    ('North', 'a1')    ('South', 'a1')    ('East', 'a1')    ('West, 'a1') Now, in sp_GetAssignedDetails, 1- Get the chosenName value (cn) from the PickOrder table for the requested division. 2- Using your query, select the top 1 record WHERE actorName > cn ... ORDER BY actorName 3- if no record is found in step 2 and there are existing records for that division (i.e., we have exhuasted the list), set the chosenName to 'a1' and repeat step 2 to start with the first name. 4- Update the chosenName column value in PickOrder table with the actorName found in step 2, so that next time we can pick the next name. -- modified (added ORDER BY clause) at 22:08 Thursday 2nd November, 2006

-- Damodar Periwal Software Tree, Inc. Simplify Data Integration

You may create a simplified and database-agnostic solution using an object-relational mapping product like NJDX. Even developing a DAL layer can get quite complicated if you have to use SQL/ADO.NET. This report shows how NJDX OR-Mapper reduced the complexity and size (70%) of the DAL code for the famous .NET Pet Shop project.

-- Damodar Periwal Software Tree, Inc. Simplify Data Integration

The following approach may be simpler. Model your object with a parent and a list of children relationship. Then use an Object Relational Mapping product like NJDX to easily retrieve any objects along with all their children without writing a single line of SQL in your application. For example, You may define a (C#) class MyEntity as follows: public class MyEntity {    int id;    int pid;            // id of the parent entity    ArrayList children; // list of children    String name;    // other fields } Then declaratively define the object relational mapping as follows: CLASS MyEntity TABLE MYENTITY    PRIMARY_KEY id    RELATIONSHIP children REFERENCES ChildrenCollection WITH id ; COLLECTION_CLASS ChildrenCollection COLLECTION_TYPE ARRAYLIST ELEMENT_TYPE MyEntity    PRIMARY_KEY pid ; Now, in your application, you can do a query like:    String predicate = ....; // WHERE clause for the top-level objects    ArrayList entities = njdx.query("MyEntity", predicate, ...); NJDX will get the top-level MyEntity objects along with its children, each child will be fetched with its own children, each grandchild will be fetched with its own children and so on ... all of this with just one query call to NJDX. The nice thing about such an approach is that it is much easier to deal with object-oriented data in your application. Further, you don't have to write the invariably complicated SLQ/ADO.NET code to fetch and properly associate all the children objects in a long hierarchy. You can also use NJDX to easily insert, update, and delete objects. Even cascading deletes can be done by specifying the mapping appropriately.

-- Damodar Periwal Software Tree, Inc. Simplify Data Integration

Well, then there is NJDX, which is beyond ADO and SQL.

Damodar Periwal Software Tree, Inc. Simplify Data Integration http://www.softwaretree.com

A small team of software engineers have re-architected the Microsoft .NET Pet Shop application by using Software Tree’s NJDX object-relational mapping (OR-Mapping) technology. Here is the abstract of the project report: The .NET Pet Shop application is designed to show the .NET best practices for building enterprise n-tier applications. In this ASP.NET based web application, classes are defined to represent domain model objects like products, users, addresses, and orders. These domain model objects are persisted in relational databases (e.g., SQL Server, Oracle) using a data access layer (DAL). The current DAL implementation, which uses raw SQL and low-level database APIs, is pretty hard to understand and maintain This report describes how such a data access layer can be replaced with a much simpler and shorter (35% less lines of code) implementation using NJDX OR-Mapping technology from Software Tree. In addition to greatly simplifying the architecture, the NJDX approach provides greater flexibility and delivers superb performance. Project Report Press Release The full implementation code ships with the NJDX software that is available for a free 30-day evaluation from Software Tree’s web site at http://www.softwaretree.com. Thank you. Damodar Periwal Software Tree, Inc. Simplify Data Integration http://www.softwaretree.com -- modified at 21:50 Wednesday 8th February, 2006

Is User3 supervised by multiple people (User1 and User2) or that is just a typo? Damodar Periwal Software Tree, Inc. Simplify Data Integration http://www.softwaretree.com

I did not mean creating one table per end-user class. In my example, the CLASSES table will hold information for all the instances of the AClass class. So essentially, you would be dealing with only a handful of tables - one for all the end-user classes, one for all the end-user structs, one for all the end-user unions, etc. Damodar Periwal Software Tree, Inc. Simplify Data Integration http://www.softwaretree.com

If your use cases are such that at most of the places you need to lookup objects of one particular type (e.g., give me a class by this name, give me all the variables with this property, ...), you may be better off storing objects of each type in a separate table. This will help in better performance because less number of records needs to be filtered during a query for a particular type of object. This will also provide better data normalization if the information for each type is quite different than that for the other ones. If you are developing your application in C# or some other managed language, you may use an OR-Mapping product to totally avoid dealing with tedious low-level infrastructire code in ADO.NET or OleDb for data integration. For example, with NJDX OR-Mapper, you may do the following: 1- Define your domain model classes (say AClass, AStruct, AUnion, AVariable, AnEnum, etc.) with properties for name, offset, etc.). For example, class AClass {   string name;   int offset;   ... } If some classes are very similar, you may define them in a class-hierarchy. 2- Define OR-Mapping declaratively like: CLASS AClass TABLE CLASSES   PRIMARY_KEY name ; CLASS AUnion TABLE UNIONS   PRIMARY_KEY name ; 3- Create the database schema using NJDXSchema tool. This will create tables CLASSES, UNIONS, etc, with proper columns and primary keys. 4- Write your application using NJDX APIs. For example, the following code will insert a new object c1 of type AClass in the database:   njdx.insert(c1, 0, null); The following code will fetch an AUnion object having name="someUnion"   // oid below can be built dynamically using program variables   Object oid = ObjectId.createObjectId("AUnion;name=someUnion");   AUnion myUnion = njdx.getObjectById(oid, true, 0, null); The following code will fetch all AClass objects into the myClasses variable.   ArrayList myClasses = njdx.query("AClass", null, -1, 0, null); If you have defined some of your classes in a hierarchy, you can fetch all the qualifying objects of all the classes in that hierarchy with one query call. Essentially, your code will be more object-oriented and easier to evolve. And you will avoid all the complexities of SQL. Damodar Periwal Software Tree, Inc. Simplify Data Integration http://www.softwaretree.com -- modified at 18:55 Tuesday 24th January,