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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-22 : 08:49:49
|
David writes "I am converting an Access 97 database to an Access 2000 project with a SQL Server 2000.
A procedure in the Access 97 db opens two recordsets. The relationship between them is one to many. The purpose is to match the records on two fields (MAT_ID and STORE) by sorting both recordsets on effectively the same index then moving thro one or the other recordset. After a move on one recordset a match is checked, if there is a match an update occurs, if there is no match the second recordset is moved forward.
How do I do this in a stored procedure?
The "Select Case" function in SQL 2000 does not appear to work in the same way. The IF..THEN.. ELSE is also different. I have tried using Cursors in SQL to create two recordsets but cannot create an updateable Cursor.
Any wisdom?
(WINDOWS NT 4, SP 5)
Dim MYDB As Database, REQD As Recordset, STOK As Recordset
Dim AVAIL, REQ_QTY, TOT_REC, REM_AVAIL As Single
Dim RETVAL As Variant
Set MYDB = DBEngine.Workspaces(0).Databases(0)
Set STOK = MYDB.OpenRecordset("MATERIAL AVAILABLE", DB_OPEN_TABLE)
Set REQD = MYDB.OpenRecordset("KITTING", DB_OPEN_TABLE)
STOK.Index = "AVAILSORT"
REQD.Index = "KITSORT"
'Allocate stock from MATERIAL AVAILABLE to requirements on KITTING
RETVAL = SysCmd(4, "Netting off stock")
STOK.MoveFirst ' Initialise Material Available
If STOK.EOF = True Then
RETVAL = SysCmd(4, "ERROR IN KITTING")
GoTo ENDPROG
Else
AVAIL = STOK.ONHAND_QTY
REQD.MoveFirst ' Initialise Material Requirements
End If
Do While Not REQD.EOF
Select Case (REQD!MAT_IDNO)
Case (STOK!MAT_IDNO) ' Required Mat_Id = Available Mat_Id, check matching Store
Select Case (REQD!STORE)
Case (STOK!STORE) ' Required Store = Available Store, allocate available material
REQ_QTY = REQD.TOT_QTY - (REQD.ISS_QTY / STOK.CONV_FACT_IS_ST) ' Allows for possibility of conversion from STOCK UOM to ISSUE UOM
REQD.Edit
If REQ_QTY > 0 Then
If REQ_QTY > AVAIL Then
REQD.KITTED = AVAIL
REQD.VISITED = "P"
Else
REQD.KITTED = REQ_QTY
REQD.VISITED = "Y"
End If
Else
REQD.KITTED = 0
REQD.VISITED = "Y"
End If
REQD.Update
AVAIL = AVAIL - REQD.KITTED ' Reduce Available stock and get next Material Requirement Record
REQD.MoveNext
etc etc etc................... " |
|
|
|
|
|
|
|
Scrolling thro recordsets in SQL Server 2000