|
|
Posted by Erland Sommarskog on 10/20/05 01:20
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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|