Microsoft SQL Server forum - Derived tables from multiple resultsets

Author

Derived tables from multiple resultsets

Keith B via SQLMonster.com

2005-10-27, 9:25 am

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 webservertalk.com
http://www.webservertalk.com/Uwe/Fo...eneral/200510/1

Erland Sommarskog

2005-10-27, 9:25 am

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

Keith B via SQLMonster.com

2005-10-27, 9:25 am

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:
>
>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

Sponsored Links