Help with sql statement

Question

I'm using sql-server 2005 and ASP.NET C#.

i have Users table with userId(int),userGender(tinyint),userAge(tinyint),userCity(tinyint). (simplified version of course).

I need to select always two fit to userID i pass to query users of opposite gender, in age range of -5 to +10 years and from the same city.

Important fact is it always must be two, so i created condition if @@rowcount<2 re-select without age and city filters.

Now the problem is that i sometimes have two returned result sets because i use first @@rowcount on a table. If i run the query.

Will it be a problem to use DataReader object to read from always second result set? Is there any other way to check how many results were selected without performing select with results?

Answer 1

Can you simplify it by using SELECT TOP 2 ?

Update: I would perform both selects all the time, union the results, and then select from them based on an order (using SELECT TOP 2) as the union may have added more than two. Its important that this next select selects the rows in order of importance, ie it prefers rows from your first select.

Alternatively, have the reader logic read the next result-set if there is one and leave the SQL alone.

Answer 2

To avoid getting two separate result sets you can do your first SELECT into a table variable and then do your @@ROWCOUNT check. If >= 2 then just select from the table variable on its own otherwise select the results of the table variable UNION ALLed with the results of the second query.

Edit: There is a slight overhead to using table variables so you'd need to balance whether this was cheaper than Adam's suggestion just to perform the 'UNION' as a matter of routine by looking at the execution stats for both approaches

SET STATISTICS IO ON

Answer 3

Would something along the following lines be of use...

SELECT *
    FROM (SELECT 1 AS prio, *
            FROM my_table M1 JOIN my_table M2
            WHERE M1.userID = supplied_user_id   AND
                  M1.userGender <> M2.userGender AND
                  M1.userAge - 5 >= M2.userAge   AND
                  M1.userAge + 15 <= M2.userAge  AND
                  M1.userCity      = M2.userCity
            LIMIT TO 2 ROWS
          UNION 
          SELECT 2 AS prio, *
              FROM my_table M1 JOIN my_table M2
              WHERE M1.userID = supplied_user_id   AND
                    M1.userGender <> M2.userGender
              LIMIT TO 2 ROWS)
    ORDER BY prio
    LIMIT TO 2 ROWS;

I haven't tried it as I have no SQL Server and there may be dialect issues.