Derived Tables From Multiple Resultsets

Hi!

I want to return a derived table along with 4 simple tables in a stored
procedure as follows:

Input parameter: @FtNum (==Order Number, selects one Order and all
associated data)

Table 1: Orders
Table 2: Items
Table 3: Instances
Table 4: StockDetails

Derived Table: for each Item that requires stock items, 1st column
should receive the ItemNo (from Items), subsequent columns should receive the
details from StockDetails via the common key field 'StockCode'.

I have so far used a 'Fetch' cursor to find all occurrences of a StockCode
within the Items table, but have been unable to figure out how to first add
the ItemNo into the temporary table.

Code is as follows:

... build #tmp_Stock

DECLARE stock_cursor CURSOR FOR
SELECT StockCode, ItemNo
FROM Items
WHERE FtNum = @FtNum
ORDER BY ItemNo

OPEN stock_cursor
FETCH NEXT FROM stock_cursor
INTO @StockCode, @ItemNo

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tmp_Stock

-- wish to insert ItemNo = @ItemNo here --

SELECT *
FROM ControlledStock
WHERE StockCode = @StockCode

FETCH NEXT FROM stock_cursor
INTO @Stockcode, @ItemNo

END

Of course there may be a much simpler way to do this!

Your help would be greatly appreciated either way.


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1