Derived tables from multiple resultsets

[Keith B via SQLMonster.com]

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...neral/200510/1

[Erland Sommarskog]

Keith B via SQLMonster.com (u2230@uwe) writes:

I want to return a derived table along with 4 simple tables in a stored
procedure as follows:
First some terminology. A derived table is a "virtual temp table in a
query" likes:

SELECT O.*
FROM Orders O
JOIN (SELECT CustomerID, maxid = MAX(OrderID)
FROM Orders
GROUP BY CustomerID) AS m ON O.Orderid = m.maxid

This lists the latest order for all customers. The thing in parenteses:

(SELECT CustomerID, maxid = MAX(OrderID)
FROM Orders
GROUP BY CustomerID)

is a derived table.

It appears that what you really are talking about is a temporary table
or some such.

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.

There is no need to use a cursor for this - in fact this is a serious
mistake to do, as it can have grave consequences on performance. With
the little information I have it looks as if the query should be:

INSERT #tmp_Stock(ItemNo, col1, col2, )
SELECT i.ItemNo, c.col1, c.col2, ...
FROM Items i
JOIN ControlledStock c ON i.StockCode = c.StockCode
Note here also some best practices:

* Always give an column list to INSERT. This makes the code more robust
and easier to maintain.
* Don't use SELECT * in production code. If the DBA would add or
remove a column from ControlledStock, your code would stop working.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Keith B via SQLMonster.com]

Thanks for your advice, at least I got the temporary table right (I was
trying to generalise with 'derived', in case the temp table was not the right
approach)!

Also, I don't use SELECT * in production code, I was just trying to save time
in a test environment.

My approach in using a cursor was to eliminate cases where a non-stock item
is supplied, when the StockCode field is null. I understand the comment
about a performance hit with a cursor, and appreciate the more direct method.
I only have previous experience with INNER / OUTER JOINS, so now I have a new
tool.

Your code worked fine anyway, and doesn't return lines where no StockCode
exists, so all's well.

Keith B
Erland Sommarskog wrote:

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

First some terminology. A derived table is a "virtual temp table in a
query" likes:

SELECT O.*
FROM Orders O
JOIN (SELECT CustomerID, maxid = MAX(OrderID)
FROM Orders
GROUP BY CustomerID) AS m ON O.Orderid = m.maxid

This lists the latest order for all customers. The thing in parenteses:

(SELECT CustomerID, maxid = MAX(OrderID)
FROM Orders
GROUP BY CustomerID)

is a derived table.

It appears that what you really are talking about is a temporary table
or some such.

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

[quoted text clipped - 12 lines]

StockCode within the Items table, but have been unable to figure out how
to first add the ItemNo into the temporary table.

There is no need to use a cursor for this - in fact this is a serious
mistake to do, as it can have grave consequences on performance. With
the little information I have it looks as if the query should be:

INSERT #tmp_Stock(ItemNo, col1, col2, )
SELECT i.ItemNo, c.col1, c.col2, ...
FROM Items i
JOIN ControlledStock c ON i.StockCode = c.StockCode

Note here also some best practices:

* Always give an column list to INSERT. This makes the code more robust
and easier to maintain.
* Don't use SELECT * in production code. If the DBA would add or
remove a column from ControlledStock, your code would stop working.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200510/1