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 colum
n
should receive the ItemNo (from Items), subsequent columns should receive th
e
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 webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200510/1

Keith B via webservertalk.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, esquel@sommarskog.se

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

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 righ
t
approach)!

Also, I don't use SELECT * in production code, I was just trying to save tim
e
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 ne
w
tool.

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

Keith B


Erland Sommarskog  wrote: 
>
>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.
> 
>[quoted text clipped - 12 lines] 
>
>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, esquel@sommarskog.se
>
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techin.../2000/books.asp


--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200510/1