Complex select in sp

[RobHVB6Sql]

Can anyone assist with a 'how to progress' reply here please.

I have a complex select statement to perform in a stored procedure which gathers information for Crystal Report which is then used for presentation.

Before begining the stored procedure I know the PersonID's I want. They are in a grid in VB and prior to this report process have been written to the tblTempClientList table.

If there are no Provider details for a client I still wish to return the person details, only the provider fields will be blank.

I have attempted to:
* create a large working table
* insert information from tblPerson here (with several where clauses)
There is also a long sting passsed in used in this fashion
"AND PersonID NOT IN (748, 921, 922, ...........)"
* insert info related to that client's provider which is also has a joining table (tblClientProvider) between tblPerson and tblProvider (where clauses here also)
- these updates are complex and I have stopped

Can you see an easier way of doing this?

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[SQLSister]

I'm not seeing why just a select won't get you what you want. Just use a left join to the table which might not have any records.

You should use real temp tables or table variables (more efficent but can't be used for everything a temp table can) rather than create a table called tblTempClientList. It is an inefficient practice to use real tables for temporary data storage for a particular process. Casuses lots of extra work such as writing to the transaction log and could create problems if two users tried to do the same thing simultaneously.

As far as passing a tabled list of variables to an sp check out these two FAQs
Passing a list of values to a Stored Procedure faq183-3979 JamesLean 8/5/03 7.9 / 7
Passing a list of values to a Stored Procedure (Part II) faq183-5207


Questions about posting. See faq183-874

 

[RobHVB6Sql]

Worked it out.

Was just a matter of doing the joins in the correct order. This little pearl of wisdom helped me through:
"Just use a left join to the table which might not have any records."

I did this join first and everything flowed from there. Will use this philosophy again.

Thanks SQLSister

not seeing why just a select won't get you what you want. Just use a left join to the table which might not have any records.

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)