SQL Server Performance

How to convert Access VBA code with string queries

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Lin100, Dec 4, 2006.

  1. Lin100 New Member

    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
  2. ranjitjain New Member

    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.

Share This Page