How do I address Multiple Selects in a Dataset

[perryg30313]

Hello Forum,
I am new to SQL Server and have inherited a database from another developer.

I have a Stored Procedure that I'm trying to use in my vb.net program to fill a form with multiple datagrids.

The Stored Proc looks like this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spGetAdjustmentDetail](@AdjustmentId uniqueidentifier)
AS
BEGIN
SELECT *
FROM tAdjustmentHeader
WHERE Id=@AdjustmentId

SELECT *
FROM tAdjustmentDetail
WHERE AdjustmentHeaderId=@AdjustmentId;

WITH tAdjustmentEx
(Id, AdjustmentHeaderId, AdjustmentDetailId,
TypeCode, MeasurementId, Value, Row)
AS
(
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM tAdjustmentEx AS I
LEFT OUTER JOIN tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
ORDER BY I.Row
END

My question is how do I address the multiple select statements in the return values of this Stored Proc? The way this is written will it return all the values from each select? or just the first one?

Any help would be appreciated,

Greg

[G_Waddell]

Hi

This isn't really a Vb.net question...

A tip for you though is to open the sql managment console, point it at your Database and run the following in a query window:

EXEC spGetAdjustmentDetail @AdjustmentId=####

(### = a value for adjustment ID)

It will output the results to the window for you.

If you're Using SQL Express or MSDE you have to get a copy of the management studio from another edition (developer, enterprise etc) although you may be able to down load it - I'm not too sure

[perryg30313]

I did that, it brings back 3 recordsets or tables. I was wondering how I could address that in a dataset? Or do I have to break the Stored Proc up into 3 single Select statements? I'm only curious because the developer that wrote this stored proc has an application that is referencing it, but I can't figure out how.

[G_Waddell]

hi,

I'd really need to see some sample code... A dataset can hold more than one table so it could possibly be that the individual record sets are being stored as datatables inside the dataset
try:

dim iTable as integer
dim strMessage as String
for iTable = 0 to Dataset.tables.count -1
   strMessage &= dataset.Tables(itable).Name
next
msgbox (strMesssage)

[G_Waddell]

Hmm...
Just thought the tables may not have names, just return the count

msgbox (dataset.Tables.count)

[perryg30313]

Thank you for the post it was very helpful.