|
|
Posted by Keith B via SQLMonster.com on 10/19/05 15:11
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/Forums.aspx/sql-server-general/200510/1
Navigation:
[Reply to this message]
|