Alex Alex - 7 months ago 20
SQL Question

Convert logic in a dynamic query from Access VBA to SQL Server view

I am converting an Access app to a web app, and I am having trouble converting this vb/SQL into views. It doesn't seem like that hard problem, but it is really throwing me for a loop.

Public Function GetProjectNumber(ByVal HeaderID As Long) As String
Dim retval As String
Dim rst As New ADODB.Recordset
Dim tempID As Long

On Error GoTo Err_Handler

rst.Open "SELECT TransferID, ProjectID FROM dbo.tblProject WHERE HeaderID = " & HeaderID
If Not (rst.EOF And rst.BOF) Then
If IsNull(rst!TransferID) Then
retval = rst!ProjectID
Else
tempID = rst!TransferID
If rst.State = adStateOpen Then rst.Close
rst.Open "SELECT ProjectID FROM dbo.tblProject WHERE HeaderID = " & tempID
If rst.EOF And rst.BOF Then
retval = "Transfer from ????"
Else
retval = "Transfer from " & rst!ProjectID
End If
End If
End If

If rst.State = adStateOpen Then rst.Close

Exit_Handler:
Set rst = Nothing
GetProjectNumber = retval
Exit Function

End Function


I tried using a nested
case
statement, but cases can't be applied to each row returned. Is there a way to do this with
IIF
? Or to make this a function?

Answer

The following query mimicks your code for all records in the table (ie for all HeaderIDs).

SELECT P1.HeaderID,
(CASE 
    WHEN P1.TransferID IS NULL THEN P1.ProjectID
    WHEN P2.ProjectID IS NULL THEN 'Transfer from ???' 
    ELSE "Transfer from " + P2.ProjectID
 END) AS Returns
FROM dbo.tblProject P1
LEFT JOIN dbo.tblProject P2 ON P1.TransferID = P2.HeaderID 

If you make a SQL-server view out of it, you can query it easily with a specific HeaderID, like you do in your VBA sub

SELECT Returns FROM the_view WHERE HeaderID='xxxxx'

And it should returns exactly the same result as your procedure