SQL Server Performance Forum – Threads Archive

How to convert Access VBA code with string queries

How to convert Access VBA code with string queries into Stored Procedure
Access 2003 and SQL 2000 Server I have a code below that work with SQL 2000 Server, and I would
like to convert it to Stored procedure.
Currently the procedure below resided in the Access 2003 front-end.
It just needs to be in the SQL 2000 Server. There are 6 combo boxes in the Form Selector that are used in the code below Forms!Selector!From_Date
Forms!Selector!To_Date
Forms!Selector!Dept
Forms!Selector!so
Forms!Selector!Item
Forms!Selector!Sectionno ///////////////////////////////////////////////////////// Private Sub get_subfrm_recs()
On Error GoTo Err_get_subfrm_recs
Dim strsql As String
Dim strDate As String
Dim strOrd As String
Dim strfrmrs As String
Dim dtstart As String
Dim dtend As String
Dim strWhere As String dtstart = "’" & Format(Forms!Selector!From_Date, "yyyymmdd") & "’"
dtend = "’" & Format(Forms!Selector!To_Date, "yyyymmdd") & "’" strsql = "SELECT DISTINCT [1_Job – Parent].SONumber, [1_Job – Parent].Department_Name,"
strsql = strsql & " [1_Job – Parent].ItemNumber, [1_Job – Parent].SectNumber,"
strsql = strsql & " [1_Job – Parent].RecordInitiateDate, [1_Job – Parent].MechUser,"
strsql = strsql & " [1_Job – Parent].ElecUser, [1_Job – Parent].GreenTagUser,"
strsql = strsql & " [1_Job – Parent].GreenTagDate,"
strsql = strsql & " Ref_DepartmentID.ID"
strsql = strsql & " FROM Ref_DepartmentID RIGHT JOIN [1_Job – Parent]"
strsql = strsql & "ON Ref_DepartmentID.ID = [1_Job – Parent].DepartmentID"
strsql = strsql & " WHERE" If Not IsNull(Forms!Selector!From_Date) And Not IsNull(Forms!Selector!To_Date) Then
strDate = strDate & " [1_Job – Parent].RecordInitiateDate"
strDate = strDate & " Between " & dtstart
strDate = strDate & " And " & dtend & " And "
End If If Len(Forms!Selector!Dept) <> 0 Then
strWhere = " Ref_DepartmentID.ID = " & Forms!Selector!Dept
End If If Len(Forms!Selector!so) <> 0 Then
strWhere = strWhere & " AND [1_Job – Parent].SONumber = " & Forms!Selector!so
End If If Len(Forms!Selector!Item) <> 0 Then
strWhere = strWhere & " AND [1_Job – Parent].ItemNumber = ‘" & Forms!Selector!Item & "’"
End If If Len(Forms!Selector!Sectionno) <> 0 Then
strWhere = strWhere & " AND [1_Job – Parent].SectNumber = ‘" & Forms!Selector!Sectionno & "’"
End If strOrd = " ORDER BY [1_Job – Parent].RecordInitiateDate DESC"
strfrmrs = strsql & strDate & strWhere & strOrd Debug.Print strsql & strDate & strWhere & strOrd Me.Q_FilteringQuery_subform.Form.RecordSource = strfrmrs If Me.Q_FilteringQuery_subform.Form.RecordsetClone.RecordCount = 0 Then
Me.Q_FilteringQuery_subform.Visible = False
Else
Me.Q_FilteringQuery_subform.Visible = True
End If Exit Sub Err_get_subfrm_recs:
MsgBox "Error: " & Err & " " & Err.Description
End Sub

---

To convert this dynamic SQL into SP, you need to understand the select and where clause and then build the same in stored procedure with all where clause column values as parameter. consider this sample: Create procedure proc_name(@Ref_DepartmentID INT)
AS
SET NOCOUNT ON
SELECT DISTINCT [1_Job – Parent].SONumber, [1_Job – Parent].Department_Name,
[1_Job – Parent].ItemNumber, [1_Job – Parent].SectNumber,
[1_Job – Parent].RecordInitiateDate, [1_Job – Parent].MechUser,
[1_Job – Parent].ElecUser, [1_Job – Parent].GreenTagUser,[1_Job – Parent].GreenTagDate,
Ref_DepartmentID.ID FROM Ref_DepartmentID RIGHT JOIN [1_Job – Parent]
ON Ref_DepartmentID.ID = [1_Job – Parent].DepartmentID
WHERE Ref_DepartmentID [email protected]_DepartmentID Ordering can be done at front end by getting the recordset by firing the above SP.
Also read about dynamic SQL to build where clause.

---

]]>