Building a list stifying separate criteria

Question

Building a list stifying separate criteria

Adam Sandler

SSCrazy

Points: 2697
More actions
June 29, 2007 at 3:54 am

#119665

Can anyone suggest a way of doing this -

I currently have a reporting feature for use by my users, bas=iaclly they get a list of all contact names who satisfy a criteria.

So A) Names starting with P , age under 40 and who are managing directors for example.

A sp generates the data needed back to the user.

the contact IDs are held in a table, along with a rnadom JObID to identify the records applying to this particular execution of the sp.

-
B)I want to allow a follow up list to be generated - say all those over 60.

The user would like to see all those who satisfy A) or B)

so the B results would be appended (distinctly) to the A) results.

How could this be done in SQL, baring in mind that multiple users may want to build such lists.

Beacuse the procedure is being run multiple times (twice) would the use of temporary tables per user be out of the question?

I hope that makes sense.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

It would helpful if you posted what you already have done, the table structure, and an idea of the results you want back. Are you looking for 2 seperate result sets or one result set witha flag indicating that it meets the followup criteria?

Rob Maurer SSC Veteran Points: 242 More actions · Answer 2

I like this approach: In the stored procedure, DECLARE a temp table and fill it by SELECTING the first set of records (A), then UNION that recordset to a second temp table SELECTING the second set of records (B), then return that UNIONed recordset. Be sure to SET NOCOUNT ON in the stored procedure.