|
|
Posted by Keith B via SQLMonster.com on 10/20/05 18:54
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, esquel@sommarskog.se
>
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200510/1
Navigation:
[Reply to this message]
|