[s_kucksdorf]

Hello all,

Like the title says I want to create a SQL (2005) stored procedure where I say:

Select a, b, c from A
Select b, c, d from B
Select c, d, e from C

And so forth. The select statements are more complicated than that, and would be difficult to use a Join on. Shown below will be some code for setting up the data adapter.

Dim cmd as new SqlCommand()
'GetTables will be they hypothetical name of the stored procedure given above.
cmd.commandText = "GetTables"
cmd.commandType = Stored Procedure
'Any parameters needed
Dim da as new DataAdapter(cmd)
dim ds as new dataset()
da.fill(ds)

Will ds be populated with the three tables I had in the stored procedure? Is that even possible? I did do some research using google, but nothing gave me an answer that I was confident enough to start coding myself, especially since SQL isn't my strong point. Thanks for any help!

Happy coding!

[keakTheGEEK]

If your stored procedure is returning multiple recordsets/tables, then you should be able to reference the recordsets via the the data set object by index:

Dim dataTable1 As DataTable = ds.Tables(0)
Dim dataTable2 As DataTable = ds.Tables(1)
Dim dataTable3 As DataTable = ds.Tables(2)

You could also use a datareader object and use the DataReader.NextResult to get to your result sets.

Also, if you possibly would like to just return one result set in your stored procedure then take a look at the UNION function in sql server. There are some constraints, so it may or may not be applicable to your particular situation.

Here is another good article about the overhead involved when using datasets...

GOOD LUCK!

[s_kucksdorf]

Thanks keak! I should really attempt to put all the data in one select statement, but considering my SQL isn't up to what it should be (or what I would like it to be at). But after looking (very briefly) at Unions it looks like they would work, except all the select statements number of columns vary. But thanks for the information, it helped! Happy coding!

Stephen