Simplifying while loop query

[sds814]

In order to improve readability and performance, can I simplify the query below to a select query?

DECLARE @ProductCodes TABLE(ProductCode nvarchar(3), IsProcessed bit DEFAULT 0)

INSERT INTO @ProductCodes(ProductCode) VALUES('ABC')
INSERT INTO @ProductCodes(ProductCode) VALUES('DEF')
INSERT INTO @ProductCodes(ProductCode) VALUES('GHI')

DECLARE @OrderData TABLE(OrderID nvarchar(3), OrderDescription nvarchar(1000))

INSERT INTO @OrderData VALUES('F12', 'Description1')
INSERT INTO @OrderData VALUES('F22', 'Description2')

DECLARE @FinalData TABLE(ProductCode nvarchar(3), OrderID nvarchar(3), OrderDescription nvarchar(1000))

DECLARE @ProductCode nvarchar(3)

WHILE ((SELECT COUNT(*) FROM @ProductCodes WHERE IsProcessed = 0) > 0)
BEGIN

	SELECT TOP 1 @ProductCode = ProductCode
	FROM @ProductCodes
	WHERE IsProcessed = 0
	
	INSERT @FinalData(ProductCode, OrderID, OrderDescription)
	SELECT @ProductCode, OrderID, OrderDescription
	FROM @OrderData
	
	UPDATE @ProductCodes
	SET IsProcessed = 1
	WHERE ProductCode = @ProductCode

END

The jist of the query is to repeat the Order Information for each Product.

Thanks for the help!

 

[SgtJarrow]

If I use:

SELECT * FROM @FinalData

As your expected output, the following should do the same as you ENTIRE WHILE loop:

SELECT
	pc.ProductCode,
	od.OrderID,
	od.OrderDescription
FROM @ProductCodes pc
CROSS JOIN @OrderData od

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[sds814]

Can I just use CROSS APPLY like below?

SELECT ProductCode
	,OrderCode
	,OrderDescription
FROM @ProductCodes PC
CROSS APPLY @OrderData OD
ORDER BY ProductCode

 

[SgtJarrow]

I'm not an expert on the differences between the two. Lots of results when i search the web so i would search for the difference and read up on it.

Found this decent explanation though ...

[pre]
CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.
2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)

CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
2.The table-valued function acts as the right input and the outer table expression acts as the left input.
3.The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
[/pre]
Hope it helps...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[sds814]

The purpose of CROSS JOIN and CROSS APPLY sound VERY similar except that CROSS APPLY can be used w/ table-valued functions.