June 7, 2010 at 12:22 pm
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
June 7, 2010 at 12:44 pm
This isn't a SQL question, this is a VB question but I'll do my best to help you ...
You can reference multiple tables within a dataset. Where your dataset is 'ds' it would be something like:
myGrid1.datasource = ds.tables(0)
myGrid2.datasource = ds.tables(1)
I don't remember the exact syntax. It's been a while since I've worked with vb.net.
June 7, 2010 at 12:49 pm
That is very helpful! Thank you.
I didn't realize I could get multi-table datasets returned from a stored proc.
😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply