SQL from VB.NET, temp tables, and overall design strategy

We currently have a Visual Basic .NET program, intended to be somewhat generic, which pulls data from our huge database, and creates reports.

Currently the general design of our VB.NET program is:

1. Run a SQL stored procedure to extract data from the database and build a table of the data we want.

2. Run another SQL stored procedure to access a 2nd table.

3. Enter a loop which reads rows from the 2nd table, one row at a time, converts the columns of each row into parameters which get passed to a 3rd stored procedure.  The 3rd stored procedure creates a 3rd table, which is the specific data we want for this specific report. Then run a report maker program which uses the 3rd table data for creating the report.

4. Repeat for all rows in the 2nd table.

This is for creating a lot of reports that are very similar, varying only in details specified in the 2nd table.

The specific stored procedures which get called can be specified for each run of the VB.NET program, making it somewhat flexible (at least that's the idea).

A. To make this generic and flexible, the VB.NET program needs to be able to handle a variety of the 2nd table, where the number of parameters (columns) may vary, and the type of each parameter (column) may vary (string or integer).

B. I see there is a way to read an entire row into an array using SqlDataReader.GetSqlValues in VB.NET. But not sure how to pass this array into a SQL stored procedure and process it within. (Table-Valued Parameters?)

C. Not sure if I can use global temp tables for all the temp tables generated. Currently permanent tables are being created (with DROP TABLE if it exists right before we create it).

Also not sure if there might be a better overall strategy. (Possibly a philosophical question with no real answer, but if there's a commonly used design pattern that might be helpful here that would be great to know.)

Thank you for any help!

(We might switch to C# instead of VB.NET . I can program in both, so doesn't really matter to me.)
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I think the overhead of creating and loading all these tables, and particularly the actions in step 3, make this an unfeasible approach.  Instead, use dynamic SQL to streamline the data conversion process to get to the final result table.
deleydSoftware EngineerAuthor Commented:
Thank you for the strategy idea. I'll look into that.

Any suggestions on passing an array of data to SQL?
(I could convert numbers to strings so all data is strings if that would help; maybe.)
Scott PletcherSenior DBACommented:
I don't know VB.NET or C#, so someone else will have to answer that part of it.

But I'd use a shared, permanent table, simply add a unique key for each different request so that each request only looks at its own rows.
deleydSoftware EngineerAuthor Commented:
OK thank you for the help.
I would write a single SQL User-Defined Function which will return a table for display.
I have written some huge conditional sql, 15+ pages which ran in sub second and accessing 28 tables.

Your current logic seems like a lot of round trip calls to the db, something I try to avoid.

This is a good article explaing the use of User-Defined Functions

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.