defering n:m lookup to stored procedure

I have a set of objects which can be members of one or more of a low number of sets (think objects which can sit in several positions inside a tree). Since these assignments need to be queried often and quickly, we store them as a single "constellation" id in the object, and then keep a table with constellation ids where each constellation is mapped to a number of sets (this also saves us from some trouble regarding distinctness, in contrast to an INNER JOIN sets WHERE set in (...) approach which might seem more obvious).

Example: object 1 and 2 are both assigned to sets 3 and 4. Instead of keeping an n:m list

object id | set id
1           3
1           4
2           3
2           4

we store a constellation id 5 for object 1 and 2, then have a table assigning sets 3 and 4 to constellation 5. Since objects tend to be assigned to common combinations of sets, this is feasible in practice, i.e. we don't need to keep the whole worst-case scenario of 2^|sets| different constellations around.

Querying for all objects within at least one of the currently visible sets s1, s2, ... we find all constellations containing s1 or s2 or ..., and then create a query WHERE constellation IN (constellation1, constellation2, constellation3, ...)

Now we're moving from Access to SQL Server, and the question is whether we should change this approach as we ARE in fact starting to create larg开发者_如何学JAVAer and larger IN-clauses.

My question is: Would this be a use-case for a stored procedure? I'd like to have a stored procedure like

SELECT * FROM objects WHERE IsInSet(s1, s2)

where this list of sets is translated into the corresponding set of constellations, and the function returns true if the object's constellation is contained in this set. Since I'm just moving to SQL Server and have never actually used stored procedures, any feedback on whether this would make sense would be highly appreciated. Specifically, I'm wondering if MSSQL would be to optimize this in a way that the constellation-to-set table becomes cached, as we're currently doing manually for the creation of the queries.

---

It sounds like you have some confusion about the differences between FUNCTIONS and STORED PROCEDURES.

Stored Procedures are exactly what the name implies - stored SQL statements that execute in a batch, and can accept parameters. They are mostly used to create consistent code and/or execution plans, and to simplify complicated processes by making them more modular and compartmentalized. They are also handy for code reuse.

User Defined Functions are passed parameters and return either a scalar value or a table, and can be used inside queries to return values that are assigned to variables or row values (like in an UPDATE statement).

It sounds like you need a table-valued-function that returns a table listing all the sets containing your values that you can then JOIN on.

Edit:

An alternative to the UDF would be using an exists clause. Without really knowing your table structure, something like:

SELECT *
FROM objects o
WHERE EXISTS ( SELECT NULL
               FROM Constellations c
               INNER JOIN ConstellationLookup cl
               WHERE c.set IN (s1,s2)
               and cl.ObjectID = o.ObjectID)

EXISTS short circuits so it runs pretty quick - it finds a hit and returns a boolean to the outer query.