Stored Proc Returnig multiple records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc Returnig multiple records

Author

Topic

Rita Bhatnagar
Posting Yak Master

172 Posts

Posted - 2002-08-01 : 16:22:41

i Have a stored Proc that is giving me multiple records.
Can somebody help me out?

CREATE Procedure qryGetBedInfo
@prmCompanyEntityID int
as
SET NOCOUNT ON
DECLARE @Type Char
--DECLARE @prmCompanyEntityID INT
DECLARE @tblWingList TABLE (WingListID INT IDENTITY, WingName NVARCHAR(15), LocationID INT)
DECLARE @tblBeds TABLE (BedID INT IDENTITY, WingName NVARCHAR(15), BedLocationID INT, WingLocationID INT)

/* Create a wing List for selected company */
INSERT INTO @tblWingList (WingName, LocationID)
SELECT Name, LocationID
FROM Location
WHERE Type = 'O' AND CompanyEntityID =@prmCompanyEntityID

/*(Loop through wing list and find the bed nodes for each wing */
DECLARE @WingIdentity INT
DECLARE @WingLocationID INT
DECLARE @ParentLocationID INT
DECLARE @tblParentNode TABLE (ParentNodeID INT IDENTITY, Type NVARCHAR(15), LocationID INT)
DECLARE @tblChildNode TABLE (ChildNodeID INT IDENTITY, Type NVARCHAR(15), LocationID INT)
DECLARE @WingName NVARCHAR(15)

SET @WingIdentity = @@RowCount

INSERT INTO @tblParentNode (Type, LocationID)
SELECT 'O' AS Type, LocationID FROM @tblWingList

WHILE @WingIdentity > 0
BEGIN -- Wing Loop
/* Find all the beds for current wing */
SET @ParentLocationID = (SELECT TOP 1 LocationID FROM @tblWingList WHERE WingListID = @WingIdentity)
Select @parentLocationID
WHILE (1 = 1)

BEGIN -- Check if current child is a bed, and if so, process loop

/* Create the current parent node by making the current child the current parent */
INSERT INTO @tblChildNode (Type, LocationID)
SELECT CurrentChild.Type, CurrentChild.LocationID
FROM @tblParentNode AS CurrentParent
INNER JOIN Location AS CurrentChild ON CurrentChild.ParentID = CurrentParent.LocationID

Select * from @tblChildNode
/* If there are no more children and no beds were found, then it is time to give up */
IF @@RowCount = 0
BREAK

/* Select only one of the types found to see if it is a bed */
SET @Type = (SELECT TOP 1 Type FROM @tblChildNode)

IF @Type = 'B'
BEGIN

/* We found beds, so get the rest of the Wing information */
SET @WingLocationID = (SELECT LocationID FROM @tblWingList WHERE WingListID = @WingIdentity)
SET @WingName = (SELECT WingName FROM @tblWingList WHERE WingListID = @WingIdentity)

/* Update the table variable with all beds for the current wing */
INSERT INTO @tblBeds (WingName, BedLocationID, WingLocationID)
SELECT @WingName AS WingName, LocationID AS BedLocationID, @WingLocationID AS WingLocationID
FROM @tblChildNode

Select * from @tblBeds
BREAK
END -- IF we found beds block

/* If we did not find the beds for this wing, then set up next children. The child becomes the parent */
DELETE FROM @tblParentNode
INSERT INTO @tblParentNode (Type, LocationID)
SELECT Type, LocationID
FROM @tblChildNode
DELETE FROM @tblChildNode

END -- While Search for Beds loop
SET @WingIdentity = @WingIdentity - 1
END -- While @WingIdentity > 1, Wing Loop

/* Get Return Value */
SET NOCOUNT OFF
SELECT * FROM @tblBeds
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources