Stored procedure to loop through results??!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure to loop through results??!

Author

Topic

billshankley1
Starting Member

11 Posts

Posted - 2010-04-07 : 07:26:44

I have two tables, a Product Table and a Product Components table.

I'm using the example of a computer just to try and accurately explain what I want to acheive.

---------------------------   ------------------------------
       product table                   product components
---------------------------   ------------------------------
product   | has parts             product    | components
---------------------------   ------------------------------
Computer  | Yes                   Computer   |   Ram
Ram       | No                    Computer   |   Processor
Processor | No                    Computer   |   PSU
PSU       | Yes                   Computer   |   Mouse
Mouse     | Yes                   Computer   |   Keyboard
Keyboard  | Yes                   Keyboard   |   Keys
                                  Keyboard   |   Board
                                  Mouse      |   Tail
                                  Mouse      |   Ears
                                  Processor  |   Heatsink
                                  Processor  |   Chip

Say I want a parts list for a computer I need to drop down through the levels and bring back the parts for a keyboard, mouse and processor etc and my returned data should look something like.

---------------------------
parts list
---------------------------
Level | Product
---------------------------
1 | Computer
2 | Ram
2 | Processor
3 | Heatshrink
3 | Chip
2 | PSU
2 | Mouse
3 | Tail
3 | Ears
2 | keyboard
3 | Keys
3 | Board

I get the feeling I need to used a stored procedure and create a temporary table to hold my result sets but i'm no SQL programmer and although maybe understanding the basics of what I need to do, actually producing a working piece of code is something else.

I'd appreciate it if someone could point me in the right direction with this one.

Cheers

Bill

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 07:37:42

you are talking about a tree (computer -> processor -> heatsink)

What version of sql server are you using?

Assuming that it is 2005 or up you want to check out a recursive common table expression (CTE)

Look for that in books online or the index of the help available through management studio.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 07:54:41

Here's a quick n dirty example



DECLARE @product TABLE (
	[product] VARCHAR(255)
	, [hasParts] BIT

	PRIMARY KEY ([product])
	)

DECLARE @productComponents TABLE (
	[product] VARCHAR(255)
	, [hasPart] VARCHAR(255)

	PRIMARY KEY ([product], [hasPart])
	)

INSERT @product ([product], [hasParts])
      SELECT 'Computer', 1
UNION SELECT 'Ram', 0      
UNION SELECT 'Processor', 0
UNION SELECT 'PSU', 1
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1

INSERT @productCOmponents ([product], [hasPart])
      SELECT 'Computer', 'Ram'
UNION SELECT 'Computer', 'Processor'
UNION SELECT 'Computer', 'PSU'
UNION SELECT 'Computer', 'Mouse'
UNION SELECT 'Computer', 'Keyboard'
UNION SELECT 'Keyboard', 'Keys'
UNION SELECT 'Keyboard', 'Board'
UNION SELECT 'Mouse', 'Tail'
UNION SELECT 'Mouse', 'Ears'
UNION SELECT 'Processor', 'Heatsink'
UNION SELECT 'Processor', 'Chip'

DECLARE @object VARCHAR(255)
SET @object = 'Computer'

; WITH partlist (
	[part]
	, [level]
	, [partPath]
	)
AS (
	SELECT
		p.[product]
		, 1
		, CAST(p.[product] AS VARCHAR(MAX))
	FROM
		@product p
	WHERE
		p.[product] = @object
	UNION ALL SELECT
		pc.[hasPart]
		, pl.[level] + 1
		, pl.[partPath] + ' -> ' + CAST(pc.[hasPart] AS VARCHAR(MAX))
	FROM
		@productComponents pc
		JOIN partList pl ON pl.[part] = pc.[product]
	)
SELECT * FROM partList
ORDER BY
	[partPath]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 07:55:18

Results:

part          level       partPath
------------- ----------- -----------------------------------
Computer      1           Computer
Keyboard      2           Computer -> Keyboard
Board         3           Computer -> Keyboard -> Board
Keys          3           Computer -> Keyboard -> Keys
Mouse         2           Computer -> Mouse
Ears          3           Computer -> Mouse -> Ears
Tail          3           Computer -> Mouse -> Tail
Processor     2           Computer -> Processor
Chip          3           Computer -> Processor -> Chip
Heatsink      3           Computer -> Processor -> Heatsink
PSU           2           Computer -> PSU
Ram           2           Computer -> Ram

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-07 : 08:00:59

I'm using SQL Server Express 2005

Thanks Transact Charlie

I want to run a report for a various products and it returns the parts list for that item on the fly.

From your coded example, correct me if i'm wrong but it looks hardcoded to return a computers parts list.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 08:58:27

the @object variable determines the starting point. If you were encapsulating something like this inside a stored procedure you'd just reference the paramater passed in in the anchor definition of the cte.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-08 : 05:57:26

Turns out the version i'm working on is Sql Express 2000.

No CTE for me although that did look like the ideal solution.

So with no CTE's what's the best way of dealing with recursive queries like this in 2000???!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 06:13:02

that sucks for a lot of reasons -- not just the lack of a CTE

sqlsever2000 express has a size limit of 2gb for any database (I think). Can you upgrade? I think the express versions are free?

In 2000 you can make a temp table (or table variable) and then write a ~~recursive~~ while loop to build the temp table. It's easier to code than the CTE but more verbose (also less efficient).

Edit: Not recursive. Just a while loop

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 06:22:01

Something like:


DECLARE @product TABLE (
	[product] VARCHAR(255)
	, [hasParts] BIT

	PRIMARY KEY ([product])
	)

DECLARE @productComponents TABLE (
	[product] VARCHAR(255)
	, [hasPart] VARCHAR(255)

	PRIMARY KEY ([product], [hasPart])
	)

INSERT @product ([product], [hasParts])
      SELECT 'Computer', 1
UNION SELECT 'Ram', 0      
UNION SELECT 'Processor', 0
UNION SELECT 'PSU', 1
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1

INSERT @productCOmponents ([product], [hasPart])
      SELECT 'Computer', 'Ram'
UNION SELECT 'Computer', 'Processor'
UNION SELECT 'Computer', 'PSU'
UNION SELECT 'Computer', 'Mouse'
UNION SELECT 'Computer', 'Keyboard'
UNION SELECT 'Keyboard', 'Keys'
UNION SELECT 'Keyboard', 'Board'
UNION SELECT 'Mouse', 'Tail'
UNION SELECT 'Mouse', 'Ears'
UNION SELECT 'Processor', 'Heatsink'
UNION SELECT 'Processor', 'Chip'

DECLARE @object VARCHAR(255)
SET @object = 'Computer'
/*
-- 2005 CTE
; WITH partlist (
	[part]
	, [level]
	, [partPath]
	)
AS (
	SELECT
		p.[product]
		, 1
		, CAST(p.[product] AS VARCHAR(MAX))
	FROM
		@product p
	WHERE
		p.[product] = @object
	UNION ALL SELECT
		pc.[hasPart]
		, pl.[level] + 1
		, pl.[partPath] + ' -> ' + CAST(pc.[hasPart] AS VARCHAR(MAX))
	FROM
		@productComponents pc
		JOIN partList pl ON pl.[part] = pc.[product]
	)
SELECT * FROM partList
ORDER BY
	[partPath]
*/

-- 2000 While loop
DECLARE @rows INT
DECLARE @level INT

SET @level = 1

DECLARE @results TABLE (
	[part] VARCHAR(255)
	, [level] INT
	, [partPath] VARCHAR(8000)
	)

INSERT INTO @results ([part], [level], [partPAth])
SELECT
	p.[product]
	, @level
	, p.[product]
FROM
	@product p
WHERE
	p.[product] = @object


SET @rows = @@ROWCOUNT

WHILE @rows > 0 BEGIN

	INSERT INTO @results ([part], [level], [partPath])
	SELECT
		pc.[hasPart]
		, @level + 1
		, p.[partPAth] + ' -> ' + pc.[hasPart]
	FROM
		@results p
		JOIN @productComponents pc ON pc.[product] = p.[part]
	WHERE
		p.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END
SELECT * FROM @results
ORDER BY
	[partPath]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-08 : 09:04:41

Thanks Charlie,

For the most part I understand the code with a couple of exceptions, my code is as follows



DECLARE @object VARCHAR(255)
SET @object = 'XLJ1844'

DECLARE @rows INT
DECLARE @level INT

SET @level = 1

DECLARE @results TABLE (
	  [Stock Code] VARCHAR(255)
	, [level] INT
	, [partPath] VARCHAR(1000)
	)

INSERT INTO @results ([StockCode], [level], [partPath])
SELECT
	  p.[StockCode]
	, @level
	, p.[StockCode]
FROM
	@StockCode p
WHERE
	p.[StockCode] = @object

SET @rows = @@ROWCOUNT

WHILE @rows > 0 BEGIN

	INSERT INTO @results ([StockCode], [level], [partPath])
	SELECT
		pc.[MakItem]
		, @level + 1
		, p.[partPath] + ' -> ' + pc.[MakeItem]
	FROM
		@results p
		JOIN @ProductInfoStockFields pc ON pc.[StockCode] = p.[StockCode]
	WHERE
		p.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END
SELECT * FROM @results
ORDER BY
	[partPath]

But i'm getting errors saying I need to declare variable '@stockcode'
and '@ProductInfoStockFields' which makes sense because I haven't declared them but they will change depending on the results surely?

Second question, in your code you use "p." and "pc.", I'm guessing these are just abbreviated names for the tables but I don't see where they are defined?

Third and final, the following piece of code (which is one of my errors) found doesn't make sense to me

@StockCode p

Just a big thank you for all your help so far, truly appreciate it.

cheers.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 10:39:32

I think you are a little confused about the tables here. Also how the result set is built up. The error you are getting:

Msg 1087, Level 15, State 2, Line 21
Must declare the table variable "@StockCode".

Is referring to the fact that you are trying to SELECT something from a TABLE called @stockCode but that you haven't declared the table in the scope. You should read up on table variables and how to use them.

I've declared the Tables in my script as TABLE VARIABLES. These are essentially "lightweight" tables that only exist in the scope of the running code. The reason I've done this in the code examples is so that you can take my whole code and run it anywhere you like to see what's happening -- there will be no legacy objects left behind.

In your production environment you'd obviously have permanent base tables that store the static data (the parts list and the the heirarachy). I've modelled these with the table variables @product and @productComponent.

I'll break down the sql I posted and explain what's happening in each step. I'll post the complete code again afterwards and you can grab that to put into a query analyser window so you can play around with it.

Step 1 -- Data and variables
All this step does is set up the two tables used later on and populate them with the data you gave in your original post. It also declares the variables used and initialises them.


DECLARE @product TABLE (
	[product] VARCHAR(255)
	, [hasParts] BIT

	PRIMARY KEY ([product])
	)

DECLARE @productComponents TABLE (
	[product] VARCHAR(255)
	, [hasPart] VARCHAR(255)

	PRIMARY KEY ([product], [hasPart])
	)

INSERT @product ([product], [hasParts])
      SELECT 'Computer', 1
UNION SELECT 'Ram', 0      
UNION SELECT 'Processor', 0
UNION SELECT 'PSU', 1
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1

INSERT @productCOmponents ([product], [hasPart])
      SELECT 'Computer', 'Ram'
UNION SELECT 'Computer', 'Processor'
UNION SELECT 'Computer', 'PSU'
UNION SELECT 'Computer', 'Mouse'
UNION SELECT 'Computer', 'Keyboard'
UNION SELECT 'Keyboard', 'Keys'
UNION SELECT 'Keyboard', 'Board'
UNION SELECT 'Mouse', 'Tail'
UNION SELECT 'Mouse', 'Ears'
UNION SELECT 'Processor', 'Heatsink'
UNION SELECT 'Processor', 'Chip'

DECLARE @object VARCHAR(255)
SET @object = 'Computer'

Step 2 - Set up a results table to put data in while we loop round. Here I've used a table variable but you could use a TEMP TABLE instead if you wanted. If there were a lot of results a temp table would be better. Here, because there is very little data, I've not declared a PRIMARY KEY or any indexes


DECLARE @results TABLE (
	[part] VARCHAR(255)
	, [level] INT
	, [partPath] VARCHAR(8000)
	)

Step 3 -- Populate the starting point into the table. Here I'm declaring some variables that will control the loop (@rows and @level) and inserting the base data into the @results table that the loop will then work off and populate the tree


DECLARE @rows INT
DECLARE @level INT

SET @level = 1

INSERT INTO @results ([part], [level], [partPAth])
SELECT
	p.[product]
	, @level
	, p.[product]
FROM
	@product p
WHERE
	p.[product] = @object


SET @rows = @@ROWCOUNT

So what's happened up to this point? The @results table now contains 1 line which is the info SELECTED from the @product table where the product column = 'Computer'. This line has the level 1. Also I've brought back the number of rows returned into the variable @rows.

Step 4 -- Cycle through and populate the rest of the results.


WHILE @rows > 0 BEGIN

	INSERT INTO @results ([part], [level], [partPath])
	SELECT
		pc.[hasPart]
		, @level + 1
		, p.[partPAth] + ' -> ' + pc.[hasPart]
	FROM
		@results p
		JOIN @productComponents pc ON pc.[product] = p.[part]
	WHERE
		p.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END

This is a pretty simple loop. What it does is check that rows were inserted into @results. If there were then it will check for components connected to the new rows inserted (tracked by the incrementing @level) and then inserted the new components found.

-- Final Set Display the results


SELECT * FROM @results
ORDER BY
	[partPath]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 10:43:56

Here's the completed script.

Please copy this whole code block and run it in a query analyser window. You'll be able to modify it to see how it works. I've included some SELECT statements from @results to show you how the table is built.


-- Step 1
DECLARE @product TABLE (
	[product] VARCHAR(255)
	, [hasParts] BIT

	PRIMARY KEY ([product])
	)

DECLARE @productComponents TABLE (
	[product] VARCHAR(255)
	, [hasPart] VARCHAR(255)

	PRIMARY KEY ([product], [hasPart])
	)

INSERT @product ([product], [hasParts])
      SELECT 'Computer', 1
UNION SELECT 'Ram', 0      
UNION SELECT 'Processor', 0
UNION SELECT 'PSU', 1
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1

INSERT @productCOmponents ([product], [hasPart])
      SELECT 'Computer', 'Ram'
UNION SELECT 'Computer', 'Processor'
UNION SELECT 'Computer', 'PSU'
UNION SELECT 'Computer', 'Mouse'
UNION SELECT 'Computer', 'Keyboard'
UNION SELECT 'Keyboard', 'Keys'
UNION SELECT 'Keyboard', 'Board'
UNION SELECT 'Mouse', 'Tail'
UNION SELECT 'Mouse', 'Ears'
UNION SELECT 'Processor', 'Heatsink'
UNION SELECT 'Processor', 'Chip'

DECLARE @object VARCHAR(255)
SET @object = 'Computer'

-- Step 2
DECLARE @results TABLE (
	[part] VARCHAR(255)
	, [level] INT
	, [partPath] VARCHAR(8000)
	)

-- Step 3
-- Show what's happening
SELECT * FROM @results
DECLARE @rows INT
DECLARE @level INT

SET @level = 1

INSERT INTO @results ([part], [level], [partPAth])
SELECT
	p.[product]
	, @level
	, p.[product]
FROM
	@product p
WHERE
	p.[product] = @object


SET @rows = @@ROWCOUNT

-- Step 4
WHILE @rows > 0 BEGIN

	-- Show what's happening
	SELECT * FROM @results

	INSERT INTO @results ([part], [level], [partPath])
	SELECT
		pc.[hasPart]
		, @level + 1
		, p.[partPAth] + ' -> ' + pc.[hasPart]
	FROM
		@results p
		JOIN @productComponents pc ON pc.[product] = p.[part]
	WHERE
		p.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END

-- Step 5
SELECT * FROM @results
ORDER BY
	[partPath]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 10:46:33

Ahd here's the dump of the @results table showing what's happening during the loop

After the first insert before the loop


part                           level       partPath
------------------------------ ----------- --------------------------------------------------
Computer                       1           Computer

And the loop

1st pass


part                           level       partPath
------------------------------ ----------- --------------------------------------------------
Computer                       1           Computer
Keyboard                       2           Computer -> Keyboard
Mouse                          2           Computer -> Mouse
Processor                      2           Computer -> Processor
PSU                            2           Computer -> PSU
Ram                            2           Computer -> Ram

2nd pass


part                           level       partPath
------------------------------ ----------- --------------------------------------------------
Computer                       1           Computer
Keyboard                       2           Computer -> Keyboard
Mouse                          2           Computer -> Mouse
Processor                      2           Computer -> Processor
PSU                            2           Computer -> PSU
Ram                            2           Computer -> Ram
Board                          3           Computer -> Keyboard -> Board
Keys                           3           Computer -> Keyboard -> Keys
Ears                           3           Computer -> Mouse -> Ears
Tail                           3           Computer -> Mouse -> Tail
Chip                           3           Computer -> Processor -> Chip
Heatsink                       3           Computer -> Processor -> Heatsink

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-12 : 04:34:51

Thanks for your help Charlie your a legend.

I think i'm close, my query executes but I'm having trouble with the 'simple' :) while loop which seems to loop forever.

I've declared two variables one which is the part code everyone know's like 'computer' and @myBomID which is the number ie. '328' given to the part by the database which no one knows hence the select statement for the @myBomID variable which in my mind will get the correct ID for the desired part code.

DECLARE @object VARCHAR(255)
DECLARE @myBomID INT
SET @object = 'Computer'
SET @myBomID = (SELECT ID FROM BomHeaders WHERE @Object = BomHeaders.BomReference)

DECLARE @BomExplosion TABLE (
	[BomID] INT
	, [StockCode] VARCHAR(255)
	, [level] INT
	, [partPath] VARCHAR(4000)
	)

DECLARE @rows INT
DECLARE @level INT

SET @level = 1

INSERT INTO @BomExplosion ([BomID],[StockCode],[level],[partPath])
SELECT
	b.[ID]
	, b.[BomReference]
	, @level
	, b.[BomReference]
FROM
	BomHeaders b
WHERE
	b.[ID] = @myBomID

SET @rows = @@ROWCOUNT

WHILE @rows > 0 BEGIN

	INSERT INTO @BomExplosion ([BomID],[StockCode],[level],[partPath])
	SELECT 
		bc.HeaderID
		, bc.[StockCode]
		, @level + 1
		, b.[partPath] + ' -> ' + bc.[StockCode]
	FROM
		@BomExplosion b
		JOIN BomComponents bc ON bc.[HeaderID] = b.[BomID]
	WHERE
		b.[level] = @level
	
	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END

SELECT * FROM @BomExplosion

ORDER BY [partPath]

My code runs but I have to cancel as it doesn't stop and when I manually stop the query it returns the following messages

(1 row(s) affected)

(3 row(s) affected)

(9 row(s) affected)

(27 row(s) affected)

(81 row(s) affected)

(243 row(s) affected)

(729 row(s) affected)

The loops obviously eternally looping and after numerous attempts and variations I can't seem to resolve the problem, any ideas?

One other small note, if I set b.BomId to null the query returns the 1st and 2nd level correctly but loops if I leave that value in the select statement.

Thanks again for your help.

Cheers, Bil'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 04:45:44

I think the problem is that you are always propagating the same ID into the @BomExplosion table. Try changing it to this and see what happens:


WHILE @rows > 0 BEGIN

	INSERT INTO @BomExplosion ([BomID],[StockCode],[level],[partPath])
	SELECT 
		bc.ID
		, bc.[StockCode]
		, @level + 1
		, b.[partPath] + ' -> ' + bc.[StockCode]
	FROM
		@BomExplosion b
		JOIN BomComponents bc ON bc.[HeaderID] = b.[BomID]
	WHERE
		b.[level] = @level
	
	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

	-- Comment OUT the below select query when happy that the loop is working
	SELECT * FROM @BomExplosion 

END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-12 : 05:00:43

The Bom (Bill of Materials) table in it's simplest form is as follows

ID, StockCode,

and the Bom Components tables is as follows

HeaderID, StockCode

The HeaderID is the the ID of the BomHeader recorded against all of it's respective components.

Removed the select statement after the loop and bc.ID isn't a column but the looping continues

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 05:13:02

OK -- Can you please post the two tables as they are and give some sample data? (ideally just dumped from the real data if it's not too sensitive)

Could you have some crazy circular reference going on?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-12 : 07:18:37

Yeah unfortunately I cannot paste the tables but i've created the following which is a pretty accurate set of the key data.

DECLARE @BomHeaders TABLE (
	[ID] INT
	, [BomReference] VARCHAR(255)

	PRIMARY KEY ([ID])
	)

DECLARE @BomComponents TABLE (
	[ID] INT
	,[HeaderID] INT
	, [StockCode] VARCHAR(255)

	PRIMARY KEY ([ID])
	)
	
DECLARE @ProductInfo TABLE (
	[StockCode] VARCHAR(255)
	, [MakeItem] BIT

	PRIMARY KEY ([StockCode])
	)

INSERT @BomHeaders ([ID], [BomReference])
      SELECT 1, 'Computer'
UNION SELECT 2, 'Keyboard'
UNION SELECT 3, 'Mouse'
UNION SELECT 4, 'Processor'
UNION SELECT 5, 'Peripherals'
UNION SELECT 6, 'FullPackage'

INSERT @BomComponents ([ID],[HeaderID], [StockCode])
      SELECT 1, 1, 'Ram'
UNION SELECT 2, 1, 'Processor'
UNION SELECT 3, 1, 'PSU'
UNION SELECT 4, 1, 'Mouse'
UNION SELECT 5, 1, 'Keyboard'
UNION SELECT 6, 2, 'Keys'
UNION SELECT 7, 2, 'Board'
UNION SELECT 8, 3, 'Tail'
UNION SELECT 9, 3, 'Ears'
UNION SELECT 10, 4, 'Heatsink'
UNION SELECT 11, 4, 'Chip'
UNION SELECT 12, 5, 'Keyboard'
UNION SELECT 13, 5, 'Mouse'
UNION SELECT 14, 6, 'Computer'
UNION SELECT 15, 6, 'Keyboard'
UNION SELECT 16, 6, 'Printer'
UNION SELECT 17, 6, 'RouterModem'
UNION SELECT 18, 6, 'EthernetCable'

INSERT @ProductInfo ([StockCode], [MakeItem])
      SELECT 'Ram', 0
UNION SELECT 'Processor', 1
UNION SELECT 'PSU', 0
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1
UNION SELECT 'Keys', 0
UNION SELECT 'Board', 0
UNION SELECT 'Tail', 0
UNION SELECT 'Ears', 0
UNION SELECT 'Heatsink', 0
UNION SELECT 'Chip', 0
UNION SELECT 'Computer', 1
UNION SELECT 'Keyboard', 1
UNION SELECT 'Printer', 0
UNION SELECT 'RouterModem', 0
UNION SELECT 'EthernetCable', 0

DECLARE @object VARCHAR(255)
DECLARE @myBomID INT
SET @object = 'Computer'
SET @myBomID = (SELECT ID FROM @BomHeaders WHERE @object = BomReference)

DECLARE @rows INT
DECLARE @level INT

SET @level = 1

DECLARE @results TABLE (
	[BomID] VARCHAR(255)
	,[StockCode] VARCHAR(255)
	,[level] INT
	,[partPath] VARCHAR(1000)
	)

INSERT INTO @results ([BomID], [StockCode], [level], [partPath])
SELECT
		b.[ID]
		, b.[BomReference]
		, @level
		, b.[BomReference]
FROM
		@BomHeaders b
WHERE
		b.[ID] = @myBomID

SET @rows = @@ROWCOUNT

WHILE @rows > 0 BEGIN

INSERT INTO @results ([BomID], [StockCode], [level], [partPath])
	
	SELECT
		b.[BomID]
		, bc.[StockCode]
		, @level + 1
		, b.[partPath] + ' -> ' + bc.[StockCode]
	FROM
		@results b
		JOIN BomComponents bc ON bc.[HeaderID] = b.[BomID]
	WHERE
		b.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END

SELECT * FROM @results

I typed this up and the query only returns one value as opposed to looping forever which doesn't make immediate sense but hopefully it's enough for you to see where i'm going wrong.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 07:48:40

OK -- your schema is a bit different to what I had expected.

Does this work?


DECLARE @BomHeaders TABLE (
	[ID] INT
	, [BomReference] VARCHAR(255)

	PRIMARY KEY ([ID])
	)

DECLARE @BomComponents TABLE (
	[ID] INT
	,[HeaderID] INT
	, [StockCode] VARCHAR(255)

	PRIMARY KEY ([ID])
	)
	
DECLARE @ProductInfo TABLE (
	[StockCode] VARCHAR(255)
	, [MakeItem] BIT

	PRIMARY KEY ([StockCode])
	)

INSERT @BomHeaders ([ID], [BomReference])
      SELECT 1, 'Computer'
UNION SELECT 2, 'Keyboard'
UNION SELECT 3, 'Mouse'
UNION SELECT 4, 'Processor'
UNION SELECT 5, 'Peripherals'
UNION SELECT 6, 'FullPackage'

INSERT @BomComponents ([ID],[HeaderID], [StockCode])
      SELECT 1, 1, 'Ram'
UNION SELECT 2, 1, 'Processor'
UNION SELECT 3, 1, 'PSU'
UNION SELECT 4, 1, 'Mouse'
UNION SELECT 5, 1, 'Keyboard'
UNION SELECT 6, 2, 'Keys'
UNION SELECT 7, 2, 'Board'
UNION SELECT 8, 3, 'Tail'
UNION SELECT 9, 3, 'Ears'
UNION SELECT 10, 4, 'Heatsink'
UNION SELECT 11, 4, 'Chip'
UNION SELECT 12, 5, 'Keyboard'
UNION SELECT 13, 5, 'Mouse'
UNION SELECT 14, 6, 'Computer'
UNION SELECT 15, 6, 'Keyboard'
UNION SELECT 16, 6, 'Printer'
UNION SELECT 17, 6, 'RouterModem'
UNION SELECT 18, 6, 'EthernetCable'

INSERT @ProductInfo ([StockCode], [MakeItem])
      SELECT 'Ram', 0
UNION SELECT 'Processor', 1
UNION SELECT 'PSU', 0
UNION SELECT 'Mouse', 1
UNION SELECT 'Keyboard', 1
UNION SELECT 'Keys', 0
UNION SELECT 'Board', 0
UNION SELECT 'Tail', 0
UNION SELECT 'Ears', 0
UNION SELECT 'Heatsink', 0
UNION SELECT 'Chip', 0
UNION SELECT 'Computer', 1
UNION SELECT 'Keyboard', 1
UNION SELECT 'Printer', 0
UNION SELECT 'RouterModem', 0
UNION SELECT 'EthernetCable', 0

DECLARE @object VARCHAR(255)
DECLARE @myBomID INT
SET @object = 'Computer'
SET @myBomID = (SELECT ID FROM @BomHeaders WHERE @object = BomReference)

DECLARE @rows INT
DECLARE @level INT

SET @level = 1

DECLARE @results TABLE (
	[BomID] VARCHAR(255)
	,[StockCode] VARCHAR(255)
	,[level] INT
	,[partPath] VARCHAR(1000)
	)

INSERT INTO @results ([BomID], [StockCode], [level], [partPath])
SELECT
		b.[ID]
		, b.[BomReference]
		, @level
		, b.[BomReference]
FROM
		@BomHeaders b
WHERE
		b.[ID] = @myBomID

SET @rows = @@ROWCOUNT

	SELECT * FROM @results
WHILE @rows > 0 BEGIN

INSERT INTO @results ([BomID], [StockCode], [level], [partPath])
	
	SELECT
		bh.[ID]
		, bc.[StockCode]
		, @level + 1
		, b.[partPath] + ' -> ' + bc.[StockCode]
	FROM
		@results b
		JOIN @BomComponents bc ON bc.[HeaderID] = b.[BomID]

		LEFT JOIN @bomHeaders bh ON bh.[BomReference] = bc.[StockCode]
	WHERE
		b.[level] = @level

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1

END

SELECT * FROM @results

Your schema is sufficiently different to the made up one I posted to mean that you have to join to a third table. this produces the expected results I hope.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

billshankley1
Starting Member

11 Posts

Posted - 2010-04-12 : 09:35:59

Cheers Charlie, LEGEND!

That join did the trick. To be honest I never expected all the help so I thought i'd make my example as straightforward as possible and hopefully get on the right path at least.

Usual forum responses seem to be vastly experienced users explaining what to do in minimal detail which for us mortals is like handing over an engine in pieces and telling them to put it together without a manual!

I hope to use this in a report which will be used extensively, whats the draw back of using variable tables against temporary tables, are variable tables stable? use lots of memory etc? What's a large query for a variable table?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 09:41:39

quote:
Originally posted by billshankley1

Cheers Charlie, LEGEND!

That join did the trick. To be honest I never expected all the help so I thought i'd make my example as straightforward as possible and hopefully get on the right path at least.

Usual forum responses seem to be vastly experienced users explaining what to do in minimal detail which for us mortals is like handing over an engine in pieces and telling them to put it together without a manual!

I hope to use this in a report which will be used extensively, whats the draw back of using variable tables against temporary tables, are variable tables stable? use lots of memory etc? What's a large query for a variable table?

No worries -- I really like those kind of recursive / tree problems.

The performance issue with using a table variable vs a temp table comes down pretty squarely on the size of the dataset(s) used. do you have any idea what the maximum result set will be? if it's more than 100 rows or so I'd be tempted to go with a temp table.

The biggest drawbacks with table variables in joins is that you can't add indexes except for the primary key index. Therefore if you were wanting to get the maximum performance out of the code you'd probably want to switch to a temp table (but only if you are talking about lots of rows in the table).

to be honest, the loop is going to slow things down a little anyway -- the report is far from row by bloody row but it's not going to be the fastest report in the world.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources