Aim: Get percentage based on if field is blank or not
Issue: Unknown amount of variables based on search and thus speed
Solution: Dynamic SQL build up
Looking for: Tips to speed up even more/spot injection as required!
Notes: removed all but one vString SQL line for viewing purposes
Dim vString As String = "" If Trim(sUPRN.Text) <> "" Then vString = "p.UPRN Like '%" & Trim(sUPRN.Text) & "%'" If vString <> "" Then vString = " and " & vString End If Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQL").ToString() 'Count total lines of data and see if they have a certifacte then get percentage Try Using connection = New SqlConnection(ConnectionString) '######### VERY IMPORTANT --> DOCURL MUST BE NULL FOR THIS TO WORK! (not blank!!) Using command As New SqlCommand("Select count(d.docurl) * 100.0 / sum(count(d.id)) over() From TblData AS D JOIN TBLPROPERTYLIST AS p ON d.UPRN = p.UPRN WHERE 1 = 1 " & vString & "", connection) connection.Open() lblCertOutput.Text = Math.Floor(command.ExecuteScalar()) End Using End Using Catch ex As Exception lblCertOutput.Text = "N/A" End Try