| Downloads |
|---|
| 7646.zip |
\[Editor's Note: Share your SQL Server discoveries, comments, problems, and solutions with other readers. Email your Reader to Reader contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $100.\]
While looking for a shortcut to avoid using a loop in my code, I discovered an efficient way to write a query to retrieve members that belong to one or more groups and have the same attributes. I try to avoid loops because they're hard to read; you might need to analyze many lines of code to determine a simple process. One process in one statement is much easier to understand. From a development point of view, a loop is cumbersome, and in most cases, it requires a temp table and some variables. Also, a loop can interfere with efficiency, depending on the hardware you run SQL Server on.
In Table 1, the Groups table contains data for an organization that gives their members different types of access to their services. The attribute FreeMemberYN in the Groups table tracks whether customers have to pay membership fees. The highlighted rows (Groups A, D, and E) have free membership.
In the Members table, I store the member number in the Member field with the Groups field as its attributes, as Table 2 shows. The highlighted rows are members who have free membership. The Groups field in the Members table contains codes (A, B, C, D, and E) for all groups that a member belongs to. Note that a member can belong to one or more groups.
When you use many Group codes, you can quickly and easily use operators such as %\[AB\]% to retrieve all members that belong to Group A or Group B. A relational design gives you duplicate members when a member belongs to both Group A and Group B.
To retrieve all Members who have free membership (Groups A, D, and E), I can write a query with the groups hard-coded:
SELECT Member FROM Members WHERE Groups LIKE '%\[ADE\]%'
Although this is an effective option for single queries, I don't recommend it because you'll have to edit the hard-coded groups of all your procedures if the free- membership groups change.
A better option than hard-coding the groups is to use a dynamic string, as Listing 1 shows. However, it uses a loop, which I'm trying to avoid. Also, it's cumbersome and has more lines of code than the optimal solution.
Listing 2 shows the best option I've found, a dynamic string using concatenation, to return all members that belong to any free membership group. This option is dynamic but concise, so it saves you programming time and is easier to maintain.