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 =@Ref_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.