Looping in SQL Server to insert row into table at top

Question

Here I have a stored procedure that seems to be taking a little longer to run than I had hoped for. What made this algorithm so lengthy was the fact when adding an element to a new table in SQL it seemed to not always get put at the top.

I also had to query a query which created another table. All in all, this algorithm is too big. Does anyone have any ideas to make it shorter?

DECLARE @RecipeQuery TABLE(
RecipeID NCHAR(100),
MaterialID NCHAR(100),
Quantity DECIMAL(18,4));
INSERT INTO @RecipeQuery
(RecipeID, MaterialID, Quantity)
SELECT RecipeID, MaterialID, Quantity
FROM Recipe
DECLARE @PrevRecipeQuery TABLE(
prevRecipeID NCHAR(100),
prevMaterialID NCHAR(100),
prevQuantity DECIMAL(18,4));
DECLARE @TempRecipeQuery TABLE(
TempRecipeID NCHAR(100),
TempMaterialID NCHAR(100),
TempQuantity DECIMAL(18,4));
DECLARE @MaterialFinder TABLE(
RID NCHAR(100),
MID NCHAR(100),
Q DECIMAL(18,4));
DECLARE @PrevMaterialFinder TABLE(
PRID NCHAR(100),
PMID NCHAR(100),
PQ DECIMAL(18,4));
DECLARE @CalcMaterial TABLE(
CalcRecipeID NCHAR(100),
CalcMaterialID NCHAR(100),
CalcQuantity DECIMAL(18,4));
DECLARE @ROWCOUNT1 INT
SET @ROWCOUNT1 = 0
DECLARE @ROWCOUNT2 INT
SET @ROWCOUNT2 = 0
DECLARE @ROWCOUNT3 INT 
SET @ROWCOUNT3 = 0
DECLARE @isDone INT
SET @isDone = 0
DECLARE @mainRowCount INT
----------------------------------------------------------------------------
--LOOP UNTIL ALL LEVELS HAVE BEEN FOUND
WHILE(@isDone != 1)
BEGIN
SET @mainRowCount = (SELECT COUNT(*) FROM @RecipeQuery)
--LOOP THROUGH EACH ROW IN THE TABLE UNTIL ALL ROWS HAVE BEEN LOOKED AT
WHILE(@ROWCOUNT1 < @mainRowCount)
BEGIN
--IF THE ROW'S MATERIAL STARTS WITH A "TempRecipeID" THAT MEANS IT IS DONE
IF(PATINDEX('R%', (SELECT TOP 1 MaterialID FROM @RecipeQuery)) = 1)
BEGIN
--INSERT THE ROW RIGHT INTO TABLE new
 INSERT INTO @CalcMaterial (CalcRecipeID, CalcMaterialID, CalcQuantity) SELECT TOP 1 RecipeID, MaterialID, Quantity FROM @RecipeQuery
 SET @ROWCOUNT3 = @ROWCOUNT3 + 1
END
--OTHERWISE
ELSE
BEGIN
--FIND THE MATERIAL'S RECIPE AND PLACE THE NEW RECIPEID AND MATERIALID INTO TABLE TempMaterialID
 INSERT INTO @PrevMaterialFinder (PRID, PMID, PQ) SELECT TOP(1) RecipeID, MaterialID, Quantity FROM @RecipeQuery
 INSERT INTO @MaterialFinder (RID, MID, Q) SELECT (SELECT TOP 1 RecipeID FROM @RecipeQuery), MaterialID, Quantity FROM Recipe WHERE RecipeID = (SELECT TOP 1 MaterialID FROM @RecipeQuery)
 DECLARE @ROWCOUNT4 INT
 SET @ROWCOUNT4 = (SELECT COUNT(*) FROM @MaterialFinder)
 WHILE(@ROWCOUNT2 < @ROWCOUNT4)
 BEGIN
 INSERT INTO @CalcMaterial
 (CalcRecipeID, CalcMaterialID, CalcQuantity)
 SELECT TOP 1 RID, MID, Q*(SELECT TOP 1 PQ FROM @PrevMaterialFinder)*0.001
 FROM @MaterialFinder
 DELETE TOP(1) FROM @MaterialFinder
 SET @ROWCOUNT2 = @ROWCOUNT2 + 1
 END
 SET @ROWCOUNT2 = 0
END
SET @ROWCOUNT2 = 0
--DELETE THE TOP ROW
DELETE TOP(1)
FROM @RecipeQuery
--INSERT THE UPDATED ROW(S) FROM TABLE TempMaterialID INTO TABLE W
INSERT INTO @TempRecipeQuery
(TempRecipeID, TempMaterialID, TempQuantity)
SELECT CalcRecipeID, CalcMaterialID, CalcQuantity
FROM @CalcMaterial
--DELETE THE UPDATED ROWS FROM TABLE TempMaterialID
DELETE FROM @MaterialFinder
DELETE FROM @PrevMaterialFinder
DELETE FROM @CalcMaterial
--INCREASE ROW COUNTER C
SET @ROWCOUNT1 = @ROWCOUNT1 + 1
--LOOP ONTO NEXT ROW
END
--AT THIS POINT ALL OF THE ROWS HAVE BEEN GONE THROUGH AT LEAST ONCE
--INSERT THE UPDATED ROWS IN TABLE W INTO THE MAIN TABLE 
INSERT INTO @RecipeQuery
(RecipeID, MaterialID, Quantity)
SELECT TempRecipeID, TempMaterialID, TempQuantity
FROM @TempRecipeQuery
--TO COMPARE
--IF THE COUNT OF BOTH TABLES IS THE SAME THEY COULD BE THE SAME
IF((SELECT COUNT(*) FROM @prevRecipeQuery) = (SELECT COUNT(*) FROM @RecipeQuery))
BEGIN
--IF THE DIFFERENCES BETWEEN THE TWO ARE NULL THEN THEY ARE THE SAME;
 IF(SELECT MaterialID FROM @RecipeQuery EXCEPT SELECT prevMaterialID FROM @prevRecipeQuery) IS NULL
--SET DONE TO 1 TO END LOOP
 SET @isDone = 1
END
--DELETE PREV TABLE FOR NEW UPDATE
DELETE FROM @prevRecipeQuery
--SET COPY INTO PREV
INSERT INTO @prevRecipeQuery
(prevRecipeID, prevMaterialID, prevQuantity)
SELECT RecipeID, MaterialID, Quantity
FROM @RecipeQuery
--SET THE ROW COUNTER BACK TO 0
SET @ROWCOUNT1 = 0
--DELETE ALL FROM THE TEMP UPDATED TABLE SO THAT IT CAN BE FILLED WITH UPDATED ROWS IF AGAIN
DELETE FROM @TempRecipeQuery
--AT THIS POINT THE LOOP WILL REPEAT IF THERE ARE ANY MORE STOCK SOLUTIONS AS MATERIALS
END
--AT THIS POINT, ALL LOOPS ARE DONE AND THE MAIN TABLE DOES NOT CONSIST OF ANY STOCK MATERIALS AS MATERIALS
--PRINT THE MAIN TABLE
SELECT RecipeID, MaterialID, Quantity FROM @RecipeQuery
ORDER BY RecipeID

• dnoeth: I doubt you need a cursor at all, looks like either some recursion or non-equi-join(s). Can you add some example data?

Answer 1

Like @dnoeth said, it would be helpful to have some example data and to also know what you want from this code. Without knowing that, we can only really comment on how well-written and formatted your code is, rather than a way to improve its functionality.

So I'm gonna take a shot in the dark here and guess that you have a self-referential tree hierarchy table and you want a better query to return the base (bottom-level) materials needed based on an input recipe.

In these kinds of cases it's often better to use recursive common table expressions or recursive CTE. A quick google should give you a more in-depth explanation than I can, but generally a CTE is like a sub-query that can reference itself, meaning it's probably the best solution here if your problem is solving a tree hierarchy.

I'll give an example that might be relevant to your use case:

First we'll make a table to store the relationships. SurrogateId is an auto-increment number (just a unique identifier), Recipe describes the parent, Material describes the child, quantity says how many of the material are part of that recipe.

declare @Materials table (
 SurrogateId int identity(1,1) 
 , Recipe varchar(100)
 , Material varchar(100)
 , Quantity int
)

Some data

insert into @Materials values 
 ('Oranges', 'Oranges', 1)
 , ('Lemons', 'Lemons', 1)
 , ('Citrus', 'Oranges', 1)
 , ('Citrus', 'Lemons', 1)
 , ('Copper', 'Copper', 1)
 , ('Tin', 'Tin', 1)
 , ('Alloy', 'Copper', 4)
 , ('Alloy', 'Tin', 1)
 , ('Mould', 'Mould', 1)
 , ('Cup', 'Alloy', 1)
 , ('Cup', 'Mould', 1)
 , ('Rope', 'Rope', 1)
 , ('Ball', 'Ball', 1)
 , ('Clapper', 'Rope', 1)
 , ('Clapper', 'Ball', 1)
 , ('Bells', 'Cup', 8)
 , ('Bells', 'Clapper', 8)
 , ('Bells', 'Rope', 6)
 , ('Walls', 'Walls', 1)
 , ('Windows', 'Windows', 1)
 , ('Ceiling', 'Ceiling', 1)
 , ('St Clement''s', 'Walls', 4)
 , ('St Clement''s', 'Windows', 10)
 , ('St Clement''s', 'Ceiling', 1)
 , ('St Clement''s', 'Bells', 1)

Base materials are defined as having the same Recipe as Material, but you can probably use a more logical solution – the method of defining base materials is not important, just make sure you somehow define it. To give an example of a relationship, Tin and Copper are base materials. Alloy is comprised of 4 Copper and 1 Tin. The bell Cup is made from 1 Alloy and 1 Mould (Mould is also a base material). Etc.

Next is the CTE.

;with items as (
 select 
 SurrogateId
 , Recipe
 , Recipe as Intermediate
 , Material
 , Quantity
 from @Materials
 where Recipe = Material
 union all
 select
 m.SurrogateId
 , m.Recipe
 , i.Recipe
 , i.Material
 , m.Quantity * i.Quantity
 from @Materials m
 inner join items i
 on i.Recipe = m.Material and m.SurrogateId <> i.SurrogateId
)

The part above the union all first populates the items table with only base materials. Then, the part below that selects all recipes that comprise of anything already in items. This is the recursion: The first step adds Tin and Copper, then the recursion adds Alloys as it comprises of Tin and Copper, then it adds Cup requiring the base materials Mould, Tin and Copper, and so on up the tree.

Now you have a table items that is like the @Materials table, except it lists only base (bottom-level) materials. Here's what my example looks like:

Recipe Material Quantity
------------------ ----------- -----------
Alloy Copper 4
Alloy Tin 1
Ball Ball 1
Bells Ball 8
Bells Rope 8
Bells Mould 8
Bells Tin 8
Bells Copper 32
Bells Rope 6
Ceiling Ceiling 1
Citrus Lemons 1
Citrus Oranges 1
Clapper Ball 1
Clapper Rope 1
Copper Copper 1
Cup Tin 1
Cup Copper 4
Cup Mould 1
Lemons Lemons 1
Mould Mould 1
Oranges Oranges 1
Rope Rope 1
St Clements Rope 6
St Clements Ball 8
St Clements Rope 8
St Clements Mould 8
St Clements Tin 8
St Clements Copper 32
St Clements Ceiling 1
St Clements Walls 4
St Clements Windows 10
Tin Tin 1
Walls Walls 1
Windows Windows 1

Now it's just a case of simply querying that table for the data you want.

I hope this answers your question, and sorry if I got the complete wrong idea!