Convert code to SQL Sp or Function

[jmikow]

I am working on optimizing a query that will need to be run from a search page on a web app we are developing. I have created the code below in the .vb file for our search page and it can easily handle the different types of searches and parameters I need to setup based on the user's input.

My question is, is there a better way to do this? I know that using a server based method (SP or UDF) would be more secure, but I'm not sure how easy it would be to obtain the same functionality.

I am going to have to add a check for file existance for each row based on the data in one of the columns. The way I know how to do this is using a cursor, but I'm not sure if that is the most efficient way.

I could be returning up to 2000 rows each time this query is run and I would like it to be as efficient as possible.

Any recommendations for how to optimize this query would be appreciated.

    Public Sub GetResults()
        ' SqlDataReader that will hold the returned results		
        Dim stmt As String
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter = Nothing
        Dim ds As DataSet = Nothing

        ' SqlConnection that will be used to execute the sql commands
        Dim connection As SqlConnection = Nothing

        Try
            connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
            connection.Open()
        Catch ex As Exception
            'Label1.Text = ex.Message '"The connection with the database can´t be established"
            Exit Sub
        End Try

        stmt = "select invoice_no," & _
                    "dateentered as [Date Entered]," & _
                    "date_billed as [Date Billed]," & _
                    "claim_no as [Claim Number]," & _
                    "records_re as [Subject Name], ltrim(rtrim(ltrim(rtrim(s.first_name)) + ' ' + ltrim(rtrim(s.last_name)))) as [Source]," & _
                    "ltrim(rtrim(ltrim(rtrim(s.city)) + ', ' + ltrim(rtrim(s.state)))) as [Source City]," & _
                    "ltrim(rtrim(ltrim(rtrim(w.plaintiff)) + ' vs. ' + ltrim(rtrim(w.defendant)))) as [Case Title]," & _
                    "f.f_DocumentID as [Doc ID]," & _
                    "left(w.subpoena_type,1) as [SubType]," & _
                    "w.date_due as [DateDue]" & _
                " from worksheets w," & _
                    "sources s," & _
                    "clients c," & _
                    "FortisApplebyRecordsRep.sysadm.DailyWork f" & _
                " where w.client_id = @ClientID" & _
                    " and w.dateentered >= DateAdd(day,-@SearchFor,GetDate())" & _
                    " and w.bill_id = c.client_id" & _
                    " and (left(rtrim(ltrim(c.client_last_name)),4) = 'CSAA'" & _
                        " or left(rtrim(ltrim(c.client_last_name)),21) = 'CALIFORNIA STATE AUTO')" & _
                    " and s.source_no = w.source_id" & _
                    " and w.invoice_no = f.invoiceno"

        Select Case ddlSortBy.SelectedValue
            Case "DateEntered"
                stmt = stmt & " order by [Date Entered]"
            Case "DateBilled"
                stmt = stmt & " order by [Date Billed]"
            Case "SubjectName"
                stmt = stmt & " order by [Subject Name]"
            Case "ClaimNumber"
                stmt = stmt & " order by [Claim Number]"
            Case "Source"
                stmt = stmt & " order by [Source]"
        End Select

        Select Case ddlSortDir.SelectedValue
            Case "a"
                stmt = stmt & " asc"
            Case "d"
                stmt = stmt & " desc"
        End Select

        ' Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
        ' We pass in database connection string, stored procedure name and value of categoryID parameterand, and a "1" for CategoryID value
        da = New SqlDataAdapter(stmt, connection)
        da.SelectCommand.Parameters.Add("@SearchFor", CType(txtSearchFor.Text, Integer))
        da.SelectCommand.Parameters.Add("@ContactID", CType(txtContactID.Text, Integer))
        da.SelectCommand.Parameters.Add("@ClientID", CType(txtClientID.Text, Integer))

        ds = New DataSet
        da.Fill(ds, "w")

        ' Populate the repeater control with the Items DataSet
        Dim objPds As PagedDataSource = New PagedDataSource
        objPds.DataSource = ds.Tables("w").DefaultView
        objPds.AllowPaging = True
        objPds.PageSize = 5
        objPds.CurrentPageIndex = CurrentPage
        TotalPages = objPds.PageCount
        lblCurrentPage.Text = "Page: " & (CurrentPage + 1).ToString() & " of " & objPds.PageCount.ToString()

        ' Disable Prev or Next buttons if necessary
        cmdFirst.Enabled = Not objPds.IsFirstPage
        cmdPrev.Enabled = Not objPds.IsFirstPage
        cmdNext.Enabled = Not objPds.IsLastPage
        cmdLast.Enabled = Not objPds.IsLastPage

        ' display results in datalist on the page.
        DataList1.DataSource = objPds
        DataList1.DataBind()

        connection.Close()
        pnlSearchResults.Visible = True
End Sub

 

[SQLBill]

I'm not big on VB, but in SQL Server you would take the script (SQL portion) and run it to create the procedure:

CREATE PROCEDURE usp_myprocedure
  @ClientID INT,
  @SearchFor INT,
  @ContractID INT
AS
 <script here>

Then you would run that on the SQL Server system and that would create the procedure. Then in your VB script you would issue:

EXEC usp_myprocedure 1,2,3

Of course you would have to put the parameters in the correct order, which is the order they are declared in the create procedure script.

Any other questions about SQL Server could be addressed in FORUM183 which is Microsoft SQL Server: Programming

-SQLBill