A stored procedure that you think you can write is no comment, it looks a bit messy. ERP related to the BOM

The erp| stored procedures are illustrated as follows: SQL Server 2000, data table name: Te (hash named) Table structure: varchar (m) varchar (20,4) Assbompoint SL finished Products 12 Finished products semi-finished Products 2 3 semi-finished products 3 2 semi-finished products 1 semi-finished 4 2 semi-finished products 1 semi-finished products 5 2 semi-finished products 3 semi-finished products 5 3


Ultimately, you need to output the results

Assbompoint SL finished semi-finished products 23 finished semi-finished products 44 semi-finished products 513

That is, the number of semi-finished products required by a unit production and the name of semi-finished products. For example semi-finished products 4=1 Unit production *2 unit semi-finished 1*2 Unit semi-finished product 4.



The stored procedure is as follows: (hehe, name is not fastidious, I did not pay too much attention to)

CREATE PROCEDURE Getbomtruelist
@AssBomName varchar (@pointName varchar), @expre varchar (500)
As
Begin
DECLARE @sl decimal (20,4)
DECLARE @expression varchar (500)
DECLARE @point varchar (20)

DECLARE @bomTop varchar (20)
Declare #point_cursor cursor Local for
Select distinct POINT,SL from te where Assbom = @pointName
Open #point_cursor

FETCH NEXT from #point_cursor
Into @point, @sl

While @ @FETCH_STATUS = 0
Begin
--if there are no subordinate nodes, add them to the datasheet
if (exists (select Point from te where Assbom = @point))
Begin
--if there are subordinate nodes, then loop again
Select @expression = RTrim (@expre) + ' * ' + RTRIM (convert (char), @sl)
EXEC (' getbomtruelist ' + @AssBomName + ', ' + @point + ', "' + @expression + '")
End
Else
Begin
INSERT into #bomTemp values (@AssBomName, @point, @sl, @expre + ' * ' + ltrim (RTrim (CONVERT (char), @sl))
End

FETCH NEXT from #point_cursor
Into @point, @sl
End

Close #point_cursor
Deallocate #point_cursor
End
Go

-------------------------------------------------------------------------------------------------------

CREATE PROCEDURE Getbomlist
As

Begin

CREATE TABLE #bomTemp (Assbom varchar (), point varchar (), SL decimal (20,4), expression varchar (500))
CREATE TABLE #bomLast (Assbom varchar (), point varchar (), SL decimal (20,4)

--Call recursive
DECLARE @bomTop varchar (20)
DECLARE bom_cursor cursor FOR
Select distinct Assbom from Te where Assbom isn't in (select Point from Te)

Open Bom_cursor

FETCH NEXT from Bom_cursor
Into @bomTop

While @ @FETCH_STATUS = 0
Begin
Exec (' getbomtruelist ' + @bomTop + ', ' + @bomTop + ', ' + ' 1 ')

FETCH NEXT from Bom_cursor
Into @bomTop
End

Close Bom_cursor
Deallocate bom_cursor

--After obtaining the data and the expression of the operation, again using the expression in the datasheet to compute
DECLARE @AssbomT varchar (20)
DECLARE @point varchar (20)
DECLARE @expression varchar (500)
DECLARE @value decimal (20,4)

DECLARE @execUpdate varchar (500)


DECLARE bom_cursor_end cursor FOR
Select Assbom,point,expression from #bomTemp
Open Bom_cursor_end
FETCH NEXT from Bom_cursor_end
Into @AssbomT, @point, @expression
While @ @FETCH_STATUS = 0
Begin
Set @execUpdate = ' Insert into #bomLast values (' + char () + RTrim (@AssbomT) + char (+) + ', ' + char (+) + RTrim (@point) + chars + ', ' + RTrim (@expression) + ') '
--set @execUpdate = ' Update #bomTemp Set sl = ' + RTrim (@expression) + ' where Assbom = ' + char (+) + RTrim (@AssbomT) + C Har + ' and point = ' + char (39) + RTrim (@point) + char ()
EXEC (@execUpdate)
FETCH NEXT from Bom_cursor_end
Into @AssbomT, @point, @expression
End

Close Bom_cursor_end
Deallocate bom_cursor_end

Select Assbom,point,sum (SL) as SL from #bomLast GROUP by Assbom,point

drop table #bomTemp
drop table #bomLast
End




-------------------------------------------------------------------------

Design ideas:

The first step is to get the top end of the finished product, because there are multiple finished products in the tested table, so you need to add a cursor to the top-level finished product, one line to handle.

After getting a finished product, then to obtain its subordinate semi-finished products, and to get the required number of semi-finished products, the number of the first set into an expression, such as 1*3, if semi-finished products and semi-finished products, that only to the semi-finished products for the same treatment, note that here is a top-end production of finished products, In the end, we can get the relationship between the top finished products and the final output, and finally get an expression.

When you save all of your relationships in a datasheet, you need to deal with it further, by calculating the expression and getting the number.

Because the primary key is not considered in the design of the original table, the use of the UPDATE statement may result in a calculation error, and there is no good way to add a new temporary table and a row to add a record to it.

The last time you perform a group by operation on the new table is the desired result.



-------------------------------------------------------------------------------------

Problem:

The stored procedure in VB has no way to use, do not know why, and do not know whether this is a bug in ADO, that is, in the SELECT * from temporary table, the recordset always returns the error that the object is closed when the operation is not allowed. This problem is also more annoying, so I finally figured out a way is to add a permanent table, the table added a column hostid, using HostID to identify the operator's machine, and ultimately return HostID related values.

But in C # inside Test everything is normal.



------------------------------------------------------------------------------------

Problems encountered in the design process

1. Cursor problem: There is a red token local in the first stored procedure that was not added to the identity at the time of writing, always prompting the cursor to be created. After joining, the problem is solved.

2, expression problem: After building an expression, you need to calculate, in SQL Server there seems to be no formula to evaluate the expression, and then return the value. Finally, we have to add a cursor to the process.

3. The problem of executing stored procedures in stored procedures: It is strange why you cannot write directly to the name of the stored procedure, but you must use exec? Also depressed for a long time.



-------------------------------------------------------------------------------------

Links to posts in related csdn

Click here