I am trying to convert an Access query that uses a custom function into an MS SQL2000 stored procedure (or in-line function) but not sure how to deal with the custom function.
Here is the SQL from Access:
Code:
SELECT T_SSKC.KitID,
T_SSKC.KitQuoteID,
T_SSKC.KitItemPartNum,
T_SSKC.CompQuoteID,
T_SSKC.CompItemPartNum,
T_SSKC_1.KitID AS CompKitID,
T_SSKC.CompQty,
BaseKit(T_SSKC.[KitID],
T_SSKC.[KitItemPartNum],
T_SSKC.[CompItemPartNum],
T_SSKC.[CompQty]) AS BaseKit,
DivideBy(T_SSKC.[KitID],
T_SSKC.[KitItemPartNum],
T_SSKC.[CompItemPartNum],
T_SSKC.[CompQty]) AS DivideBy
FROM T_SSKC
INNER JOIN T_SSKC AS T_SSKC_1
ON T_SSKC.CompItemPartNum = T_SSKC_1.KitItemPartNum
GROUP BY T_SSKC.KitID,
T_SSKC.KitQuoteID,
T_SSKC.KitItemPartNum,
T_SSKC.CompQuoteID,
T_SSKC.CompItemPartNum,
T_SSKC_1.KitID,
T_SSKC.CompQty
And here is the Access custom function:
Code:
Public Function BaseKit(KitID, KitItemPartNum, CompItemPartNum, CompQty) As Variant
'This drills down until it finds the actual items needed to produce a kit
Dim rstKits As Recordset, intBaseId As Integer, strFloatingCompItem As String
If IsNull(CompItemPartNum) = False Then
intBaseId = KitID
strFloatingCompItem = CompItemPartNum
Set rstKits = CurrentDb.OpenRecordset("T-SetupSheetKitComponents")
rstKits.MoveFirst
Do Until rstKits.NoMatch = True
rstKits.FindFirst "[KitItemPartNum]=""" & strFloatingCompItem & """"
If rstKits.NoMatch = False Then intBaseId = rstKits.Fields("[KitID]")
strFloatingCompItem = rstKits.Fields("[CompItemPartNum]")
Loop
BaseKit = intBaseId
Else
BaseKit = Null
End If
End Function
Do I turn the custom Access function into a MS SQL function or do I somehow do everything in one single, but long, stored procedure.
Either way, I probably also need some assistance in re-writing the Access custom function since I am not sure how to address converting it to anything.
Function in query
