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-27 : 10:18:15
|
David writes "I am converting an Access 97 database to an Access 2000 project with a SQL Server 2000. I need to create a stored procedure which will do what the attached VBA code will do.
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 Access 97 has no equivalent function in SQL 2000. The IF..THEN.. ELSE is also different. I have tried using Cursors in SQL to create two recordsets but cannot create an updateable Cursor. Dose anyone have any tips on creating and scrolling thro two cursors, only one of which is updateable. When I run the code I get an error message that "cursor is READ only" even when is is declared as scrollable for update.
Any wisdom?
(WINDOWS NT 4, SP 5)
Private Sub AllocateStock()
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
If REQD.EOF = True Then ' Quit if no more Material Requirements
Exit Do
End If
If AVAIL <= 0 Then ' If used all stock get next Material Available
STOK.MoveNext
If STOK.EOF = False Then
AVAIL = STOK.ONHAND_QTY
Else
Do While REQD.EOF = False
REQD.Edit
REQD.VISITED = "S"
REQD.Update
REQD.MoveNext
Loop
Exit Do
End If
End If
|
|
|
|
|
|
|
|
Can VBA code like this be written in T-SQL