Advanced Search using CheckboxList

Question

I have an advanced search that I need to implement for a website using C# and SQL Server. Here is the basic idea:

1. User selects required search criteria - city, state, zip
2. User can select optional search criteria. This search criteria is a checkbox list. The checkbox list is databound to a list of criteria from sql server.
3. The search happens for all required search criteria and if any optional criteria was selected then the item needs to match all of the optional criteria.

I have the required search criteria working, but I can't figure out how to do the optional criteria. The issue comes in that the user can select multiple criteria in the checkboxlist and all of those items need to be matched. I also need to implement paging and sorting (which I have working), but this means that the search needs to happen in SQL.

Has anyone done something like this before and has some ideas on the best way to do it?

Answer 1

Dynamically build your SQL, like in the following:

    Private Sub LoadData()
        'Initialize first
        grdResults.DataSource = Nothing
        grdResults.DataBind()
        grdResults.Visible = False

        grdResultsRequestedBy.DataSource = Nothing
        grdResultsRequestedBy.DataBind()
        grdResultsRequestedBy.Visible = False

        grdResultsCallDate.DataSource = Nothing
        grdResultsCallDate.DataBind()
        grdResultsCallDate.Visible = False

        btnExportToExcel.Visible = False
        lblNoneFound.Visible = True

        ' We have to include null values when no value for a given field is provided.
        ' The like search "%" does not return rows with null values!!
        Dim strShiftType As String
        If (ddlShiftType.SelectedValue = "<- All ->") Then
            strShiftType = ""
        Else
            strShiftType = " and sr.Shift_Type_ID = " & "'" & ddlShiftType.SelectedValue & "' "
        End If

        ' Only allow good dates
        ' If one of Start or End is populated the other one will get the same value assigned.
        ' This avoids unnecessary entry if you deal with a range of one day only!
        If txtShiftDateRangeStart.Text <> "" And txtShiftDateRangeEnd.Text = "" Then
            txtShiftDateRangeEnd.Text = txtShiftDateRangeStart.Text
        End If
        If txtShiftDateRangeStart.Text = "" And txtShiftDateRangeEnd.Text <> "" Then
            txtShiftDateRangeStart.Text = txtShiftDateRangeEnd.Text
        End If
        Dim strShiftDate As String
        If txtShiftDateRangeStart.Text <> "" And txtShiftDateRangeEnd.Text <> "" And IsDate(txtShiftDateRangeStart.Text) And IsDate(txtShiftDateRangeEnd.Text) Then
            strShiftDate = " and (sr.shift_date between " & "'" & txtShiftDateRangeStart.Text & "' and '" & txtShiftDateRangeEnd.Text & "')"
        Else
            strShiftDate = ""
            txtShiftDateRangeStart.Text = ""
            txtShiftDateRangeEnd.Text = ""
        End If

        Dim strRequestedBy As String
        If (ddlRequestedBy.SelectedValue = "<- All ->") Then
            strRequestedBy = ""
        Else
            strRequestedBy = " and bi.Requested_By = " & "'" & ddlRequestedBy.SelectedValue & "' "
        End If

        Dim txtCallNumberlike As String
        If (txtCallNumber.Text & "%" = "%") Then
            txtCallNumberlike = ""
        Else
            txtCallNumberlike = " and (bi.Call_Number LIKE @CallNumber) "
        End If

        ' Only allow good dates
        ' If one of Start or End is populated the other one will get the same value assigned.
        ' This avoids unnecessary entry if you deal with a range of one day only!
        If txtCallDateRangeStart.Text <> "" And txtCallDateRangeEnd.Text = "" Then
            txtCallDateRangeEnd.Text = txtCallDateRangeStart.Text
        End If
        If txtCallDateRangeStart.Text = "" And txtCallDateRangeEnd.Text <> "" Then
            txtCallDateRangeStart.Text = txtCallDateRangeEnd.Text
        End If
        Dim strCallDate As String
        If txtCallDateRangeStart.Text <> "" And txtCallDateRangeEnd.Text <> "" And IsDate(txtCallDateRangeStart.Text) And IsDate(txtCallDateRangeEnd.Text) Then
            strCallDate = " and (cast(bi.Call_date as datetime) between " & "'" & txtCallDateRangeStart.Text & "' and '" & txtCallDateRangeEnd.Text & "')"
        Else
            strCallDate = ""
            txtCallDateRangeStart.Text = ""
            txtCallDateRangeEnd.Text = ""
        End If

        Dim strDispatcher As String
        If (ddlDispatcher.SelectedValue = "<- All ->") Then
            strDispatcher = ""
        Else
            strDispatcher = " and bi.Dispatcher_ID = " & "'" & ddlDispatcher.SelectedValue & "' "
        End If

        Dim strEmployeeCompletingBillingInquiry As String
        If (ddlEmployeeCompletingBillingInquiry.SelectedValue = "<- All ->") Then
            strEmployeeCompletingBillingInquiry = ""
        Else
            strEmployeeCompletingBillingInquiry = " and bi.Employee_Completing_Billing_Inquiry_ID = " & "'" & ddlEmployeeCompletingBillingInquiry.SelectedValue & "' "
        End If

        ' Only allow good dates
        Dim strWhenCreated As String
        ' If one of Start or End is populated the other one will get the same value assigned.
        ' This avoids unnecessary entry if you deal with a range of one day only!
        If txtWhenCreatedRangeStart.Text <> "" And txtWhenCreatedRangeEnd.Text = "" Then
            txtWhenCreatedRangeEnd.Text = txtWhenCreatedRangeStart.Text
        End If
        If txtWhenCreatedRangeStart.Text = "" And txtWhenCreatedRangeEnd.Text <> "" Then
            txtWhenCreatedRangeStart.Text = txtWhenCreatedRangeEnd.Text
        End If
        If txtWhenCreatedRangeStart.Text <> "" And txtWhenCreatedRangeEnd.Text <> "" And IsDate(txtWhenCreatedRangeStart.Text) And IsDate(txtWhenCreatedRangeEnd.Text) Then
            strWhenCreated = " and (cast(convert(char(10), bi.When_Created, 101) as datetime) between " & "'" & txtWhenCreatedRangeStart.Text & "' and '" & txtWhenCreatedRangeEnd.Text & "')"
        Else
            strWhenCreated = ""
            txtWhenCreatedRangeStart.Text = ""
            txtWhenCreatedRangeEnd.Text = ""
        End If

        Dim strWhoCreated As String
        If (ddlWhoCreated.SelectedValue = "<- All ->") Then
            strWhoCreated = ""
        Else
            strWhoCreated = " and bi.Who_Created = " & "'" & ddlWhoCreated.SelectedValue & "' "
        End If

        ' Only allow good dates
        ' If one of Start or End is populated the other one will get the same value assigned.
        ' This avoids unnecessary entry if you deal with a range of one day only!
        If txtWhenUpdatedRangeStart.Text <> "" And txtWhenUpdatedRangeEnd.Text = "" Then
            txtWhenUpdatedRangeEnd.Text = txtWhenUpdatedRangeStart.Text
        End If
        If txtWhenUpdatedRangeStart.Text = "" And txtWhenUpdatedRangeEnd.Text <> "" Then
            txtWhenUpdatedRangeStart.Text = txtWhenUpdatedRangeEnd.Text
        End If
        Dim strWhenUpdated As String
        If txtWhenUpdatedRangeStart.Text <> "" And txtWhenUpdatedRangeEnd.Text <> "" And IsDate(txtWhenUpdatedRangeStart.Text) And IsDate(txtWhenUpdatedRangeEnd.Text) Then
            strWhenUpdated = " and (cast(convert(char(10), bi.When_Updated, 101) as datetime) between " & "'" & txtWhenUpdatedRangeStart.Text & "' and '" & txtWhenUpdatedRangeEnd.Text & "')"
        Else
            strWhenUpdated = ""
            txtWhenUpdatedRangeStart.Text = ""
            txtWhenUpdatedRangeEnd.Text = ""
        End If

        Dim strWhoUpdated As String
        If (ddlWhoUpdated.SelectedValue = "<- All ->") Then
            strWhoUpdated = ""
        Else
            strWhoUpdated = " and bi.Who_Updated = " & "'" & ddlWhoUpdated.SelectedValue & "' "
        End If

        Dim strIsDeleted As String
        If (ddlIsDeleted.SelectedValue = "<- All ->") Then
            strIsDeleted = ""
        Else
            If ddlIsDeleted.SelectedValue = "False" Then
                strIsDeleted = " and (bi.IsDeleted = 'F' or bi.IsDeleted is null)"
            Else
                strIsDeleted = " and bi.IsDeleted = 'T'"
            End If
        End If

        ' Only allow good dates
        ' If one of Start or End is populated the other one will get the same value assigned.
        ' This avoids unnecessary entry if you deal with a range of one day only!
        If txtWhenDeletedRangeStart.Text <> "" And txtWhenDeletedRangeEnd.Text = "" Then
            txtWhenDeletedRangeEnd.Text = txtWhenDeletedRangeStart.Text
        End If
        If txtWhenDeletedRangeStart.Text = "" And txtWhenDeletedRangeEnd.Text <> "" Then
            txtWhenDeletedRangeStart.Text = txtWhenDeletedRangeEnd.Text
        End If
        Dim strWhenDeleted As String
        If txtWhenDeletedRangeStart.Text <> "" And txtWhenDeletedRangeEnd.Text <> "" And IsDate(txtWhenDeletedRangeStart.Text) And IsDate(txtWhenDeletedRangeEnd.Text) Then
            strWhenDeleted = " and (cast(convert(char(10), bi.When_Deleted, 101) as datetime) between " & "'" & txtWhenDeletedRangeStart.Text & "' and '" & txtWhenDeletedRangeEnd.Text & "')"
        Else
            strWhenDeleted = ""
            txtWhenDeletedRangeStart.Text = ""
            txtWhenDeletedRangeEnd.Text = ""
        End If

        Dim strWhoDeleted As String
        If (ddlWhoDeleted.SelectedValue = "<- All ->") Then
            strWhoDeleted = ""
        Else
            strWhoDeleted = " and bi.Who_Deleted = " & "'" & ddlWhoDeleted.SelectedValue & "' "
        End If

        Dim Parameters_Group_In As String
        Dim Parameters_Effective_Date_In As String
        Parameters_Group_In = "REQUESTEDBY"
        Parameters_Effective_Date_In = Now()

        Dim SelectString As String = ""
        Dim Error_Message As String = ""
        Error_Message = BuildSelectString(SelectString)

        Dim strCommand As String = ""
        If ViewState("Submit") = "Submit" Then
            strCommand += " select " & SelectString
            strCommand += " from "
            strCommand += " ( "
            strCommand += " select convert(Char(10), sr.shift_date, 101) as ShiftDate, "
            strCommand += "       case when st.Shift_Desc is null then '' else st.Shift_Desc end + ' / ' + case when st.Shift_Start_Time is null then '' else st.Shift_Start_Time end + ' / ' + case when st.Shift_End_Time is null then '' else st.Shift_End_Time end + ' / ' + cast(st.Shift_Type_ID as varchar) as ShiftType, "
            strCommand += "       ParamRequestedBy.ParamRequestedByDesc as RequestedBy, "
            strCommand += "       bi.Call_Number as CallNumber, "
            strCommand += "       convert(Char(10), bi.Call_Date, 101) as CallDate, "
            strCommand += "       case when dispatcher.Last_Name is null then '' else dispatcher.Last_Name end + ', ' + case when dispatcher.First_Name is null then '' else dispatcher.First_Name end + ' / ' + cast(dispatcher.Employee_ID as varchar) as Dispatcher, "
            strCommand += "       (Select case when Employee.Last_Name is null then '' else Employee.Last_Name end + ', ' + case when Employee.First_Name is null then '' else Employee.First_Name end + ' / ' + cast(Employee.Employee_ID as varchar) from Employee where Employee.Employee_ID = bi.Dispatcher2_ID) as Dispatcher2, "
            strCommand += "       (Select case when Employee.Last_Name is null then '' else Employee.Last_Name end + ', ' + case when Employee.First_Name is null then '' else Employee.First_Name end + ' / ' + cast(Employee.Employee_ID as varchar) from Employee where Employee.Employee_ID = bi.Dispatcher3_ID) as Dispatcher3, "
            strCommand += "       bi.comments as Comments, "
            strCommand += "       case when completing.Last_Name is null then '' else completing.Last_Name end + ', ' + case when completing.First_Name is null then '' else completing.First_Name end + ' / ' + cast(completing.Employee_ID as varchar) as EmployeeCompletingBillingInquiry, "
            strCommand += "       bi.When_Created as WhenCreated, "
            strCommand += "       case when Who_Created.Last_Name is null then '' else Who_Created.Last_Name end + ', ' + case when Who_Created.First_Name is null then '' else Who_Created.First_Name end + ' / ' + cast(Who_Created.Employee_ID as varchar) as WhoCreated, "
            strCommand += "       bi.When_Updated as WhenUpdated, "
            strCommand += "       case when Who_Updated.Last_Name is null then '' else Who_Updated.Last_Name end + ', ' + case when Who_Updated.First_Name is null then '' else Who_Updated.First_Name end + ' / ' + cast(Who_Updated.Employee_ID as varchar) as WhoUpdated, "
            strCommand += "       case when bi.IsDeleted = 'F' or bi.IsDeleted is null then 'False' else 'True' end as IsDeleted, "
            strCommand += "       bi.When_Deleted as WhenDeleted, "
            strCommand += "       case when Who_Deleted.Last_Name is null then '' else Who_Deleted.Last_Name end + ', ' + case when Who_Deleted.First_Name is null then '' else Who_Deleted.First_Name end + ' / ' + cast(Who_Deleted.Employee_ID as varchar) as WhoDeleted "
            strCommand += "  from billing_inquiries bi "
            strCommand += "       inner      join Shift_Report sr          on bi.Shift_Report_ID                        = sr.Shift_Report_ID "
            strCommand += "       inner      join Shift_Types  st          on sr.Shift_Type_ID                          = st.Shift_Type_ID "
            strCommand += "       inner      join Employee     dispatcher  on bi.Dispatcher_ID                          = dispatcher.employee_id "
            strCommand += "       inner      join Employee     completing  on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id "
            strCommand += "       left outer join Employee     Who_Created on bi.Who_Created                             = Who_Created.Employee_ID "
            strCommand += "       left outer join Employee     Who_Updated on bi.Who_Updated                             = Who_Updated.Employee_ID "
            strCommand += "       left outer join Employee     Who_Deleted on bi.Who_Deleted                             = Who_Deleted.Employee_ID "
            strCommand += "       inner      join ( "
            strCommand += "                        select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc "
            strCommand += "                          from Parameters_Details pd1, Parameters_Header ph "
            strCommand += "                         where ph.parameters_group  = pd1.parameters_group "
            strCommand += "                           And pd1.parameters_group = @Parameters_Group "
            strCommand += "                           And pd1.parameters_effective_date = "
            strCommand += "                               (select max(pd2.parameters_effective_date) "
            strCommand += "                                  from parameters_details pd2 "
            strCommand += "                                 where pd2.parameters_group           = pd1.parameters_group "
            strCommand += "                                   and pd2.parameters_id              = pd1.parameters_id "
            strCommand += "                                   and pd2.parameters_effective_date <= @Parameters_Effective_Date) "
            strCommand += "                       ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID "
            strCommand += " where 1=1 "
            strCommand += strShiftType
            strCommand += strShiftDate
            strCommand += strRequestedBy
            strCommand += txtCallNumberlike
            strCommand += strCallDate
            strCommand += strDispatcher
            strCommand += strEmployeeCompletingBillingInquiry
            strCommand += strWhenCreated
            strCommand += strWhoCreated
            strCommand += strWhenUpdated
            strCommand += strWhoUpdated
            strCommand += strIsDeleted
            strCommand += strWhenDeleted
            strCommand += strWhoDeleted
            strCommand += " ) as Details "
        End If

        If ViewState("Submit") = "SubmitRequestedBy" Then
            strCommand += "select ParamRequestedBy.ParamRequestedByDesc as RequestedBy, "
            strCommand += "       count(*) as Count "
            strCommand += "  from billing_inquiries bi "
            strCommand += "       inner      join Shift_Report sr          on bi.Shift_Report_ID                        = sr.Shift_Report_ID "
            strCommand += "       inner      join Shift_Types  st          on sr.Shift_Type_ID                          = st.Shift_Type_ID "
            strCommand += "       inner      join Employee     dispatcher  on bi.Dispatcher_ID                          = dispatcher.employee_id "
            strCommand += "       inner      join Employee     completing  on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id "
            strCommand += "       left outer join Employee     Who_Created on bi.Who_Created                             = Who_Created.Employee_ID "
            strCommand += "       left outer join Employee     Who_Updated on bi.Who_Updated                             = Who_Updated.Employee_ID "
            strCommand += "       left outer join Employee     Who_Deleted on bi.Who_Deleted                             = Who_Deleted.Employee_ID "
            strCommand += "       inner      join ( "
            strCommand += "                        select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc "
            strCommand += "                          from Parameters_Details pd1, Parameters_Header ph "
            strCommand += "                         where ph.parameters_group  = pd1.parameters_group "
            strCommand += "                           And pd1.parameters_group = @Parameters_Group "
            strCommand += "                           And pd1.parameters_effective_date = "
            strCommand += "                               (select max(pd2.parameters_effective_date) "
            strCommand += "                                  from parameters_details pd2 "
            strCommand += "                                 where pd2.parameters_group           = pd1.parameters_group "
            strCommand += "                                   and pd2.parameters_id              = pd1.parameters_id "
            strCommand += "                                   and pd2.parameters_effective_date <= @Parameters_Effective_Date) "
            strCommand += "                       ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID "
            strCommand += " where 1=1 "
            strCommand += strShiftType
            strCommand += strShiftDate
            strCommand += strRequestedBy
            strCommand += txtCallNumberlike
            strCommand += strCallDate
            strCommand += strDispatcher
            strCommand += strEmployeeCompletingBillingInquiry
            strCommand += strWhenCreated
            strCommand += strWhoCreated
            strCommand += strWhenUpdated
            strCommand += strWhoUpdated
            strCommand += strIsDeleted
            strCommand += strWhenDeleted
            strCommand += strWhoDeleted
            strCommand += " group by ParamRequestedBy.ParamRequestedByDesc "
            strCommand += " order by ParamRequestedBy.ParamRequestedByDesc "
        End If

        If ViewState("Submit") = "SubmitCallDate" Then
            strCommand += "select DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end as YearMonth, "
            strCommand += "       count(*) as Count "
            strCommand += "  from billing_inquiries bi "
            strCommand += "       inner      join Shift_Report sr          on bi.Shift_Report_ID                        = sr.Shift_Report_ID "
            strCommand += "       inner      join Shift_Types  st          on sr.Shift_Type_ID                          = st.Shift_Type_ID "
            strCommand += "       inner      join Employee     dispatcher  on bi.Dispatcher_ID                          = dispatcher.employee_id "
            strCommand += "       inner      join Employee     completing  on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id "
            strCommand += "       left outer join Employee     Who_Created on bi.Who_Created                             = Who_Created.Employee_ID "
            strCommand += "       left outer join Employee     Who_Updated on bi.Who_Updated                             = Who_Updated.Employee_ID "
            strCommand += "       left outer join Employee     Who_Deleted on bi.Who_Deleted                             = Who_Deleted.Employee_ID "
            strCommand += "       inner      join ( "
            strCommand += "                        select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc "
            strCommand += "                          from Parameters_Details pd1, Parameters_Header ph "
            strCommand += "                         where ph.parameters_group  = pd1.parameters_group "
            strCommand += "                           And pd1.parameters_group = @Parameters_Group "
            strCommand += "                           And pd1.parameters_effective_date = "
            strCommand += "                               (select max(pd2.parameters_effective_date) "
            strCommand += "                                  from parameters_details pd2 "
            strCommand += "                                 where pd2.parameters_group           = pd1.parameters_group "
            strCommand += "                                   and pd2.parameters_id              = pd1.parameters_id "
            strCommand += "                                   and pd2.parameters_effective_date <= @Parameters_Effective_Date) "
            strCommand += "                       ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID "
            strCommand += " where 1=1 "
            strCommand += strShiftType
            strCommand += strShiftDate
            strCommand += strRequestedBy
            strCommand += txtCallNumberlike
            strCommand += strCallDate
            strCommand += strDispatcher
            strCommand += strEmployeeCompletingBillingInquiry
            strCommand += strWhenCreated
            strCommand += strWhoCreated
            strCommand += strWhenUpdated
            strCommand += strWhoUpdated
            strCommand += strIsDeleted
            strCommand += strWhenDeleted
            strCommand += strWhoDeleted
            strCommand += " group by DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end "
            strCommand += " order by DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end "
        End If

        Dim Parameters_Hashtable As New Hashtable
        Parameters_Hashtable("@CallNumber") = txtCallNumber.Text & "%"
        Parameters_Hashtable("@Parameters_Group") = Parameters_Group_In
        Parameters_Hashtable("@Parameters_Effective_Date") = Parameters_Effective_Date_In

        Dim dtBillingInquiries As DataTable = DataAccess.GetDataAsDataSet( _
                                                  strCommand, _
                                                  "BillingInquiries", _
                                                  Parameters_Hashtable, _
                                                  Error_Message _
                                                 ).Tables("BillingInquiries")
        If (Error_Message = "") Then
            If ViewState("Submit") = "Submit" Then
                grdResults.DataSource = dtBillingInquiries
                If dtBillingInquiries.Rows.Count > 0 Then
                    grdResults.Visible = True
                    grdResults.DataBind()
                    lblNoneFound.Visible = False
                    btnExportToExcel.Visible = True
                End If
            End If
            If ViewState("Submit") = "SubmitRequestedBy" Then
                grdResultsRequestedBy.DataSource = dtBillingInquiries
                If dtBillingInquiries.Rows.Count > 0 Then
                    grdResultsRequestedBy.Visible = True
                    grdResultsRequestedBy.DataBind()

                    SetGraph(dtBillingInquiries)

                    lblNoneFound.Visible = False
                    btnExportToExcel.Visible = True
                End If
            End If
            If ViewState("Submit") = "SubmitCallDate" Then
                grdResultsCallDate.DataSource = dtBillingInquiries
                If dtBillingInquiries.Rows.Count > 0 Then
                    grdResultsCallDate.Visible = True
                    grdResultsCallDate.DataBind()

                    SetGraph(dtBillingInquiries)

                    lblNoneFound.Visible = False
                    btnExportToExcel.Visible = True
                End If
            End If
        Else
            lblMessage.Text = Error_Message
        End If

    End Sub

Answer 2

You're going to have to loop through the list collection and use that to modify your where clause.

Of course, unless this is an app using absolutely no sensitive data, and the user you're using to connect to the DB has absolutely no access to sensitive data, you should be using stored procedures rather than queries in code, and you should never use user input directly to build an SQL statement. This means that you will have to plan on using if statements in your stored procedures as well.

Your stored procedure would look something like this:

CREATE PROCEDURE [dbo].[SearchProcedure]

@FilterByLoginID bit,
@LoginId varchar(10)
 AS

DECLARE @SqlQuery varchar (250);

SET @SqlQuery = 'Select FirstName, LastName FROM CustomerList'
if @FilterByLoginID = 1
BEGIN
  SET @SqlQuery = @SqlQuery + ' WHERE LoginId = ' + CAST(@LoginID AS varchar(10))

END

Execute(@SqlQuery)

Here's a link to get you going... http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx

Answer 3

you can have a select like this

select * from myTable m
where m.optional1==$value or $value==-1
      m.optional2==$value or $value==-1
..

note that "-1" must not be a valid value in the domain.

Answer 4

I'm assuming you have something like this:

• city (string)
• state (string)
• zip (number / string)
• optional criteria (string items) : for the purposes of this example, I'm going to assume a software domain - say it's something like a television provider. So a sample option is: hasExpandedTVPackage, hasHBOpackage, etc as checkBoxes only provide a bool on/off piece of data.

So for example, you have two checkbox items on the page: [ ] Also has Expanded Package [ ] Also has HBO. etc - basically a 'find all guys who live in city, state, zip (and if say HBO is selected), and also has paid for the HBO package'.

Now, if you just wrote the SQL yourself, it would be "SELECT * FROM Users WHERE State = @State AND City = @City AND ZipCode = @ZipCode AND HasHboPackage = 1". But we would need to use dynamic SQL to put that together.

My suggestion is to put together a table and create the extra bits of SQL there, so all you pass in to the stored proc are the IDs of the seleted items (no transmission of input data). Sample code:

DECLARE @Items TABLE
(
    ID INT,
    DisplayName VARCHAR(200),
    SQLStatement VARCHAR(200)
)

INSERT INTO @Items (ID, DisplayName, SQLStatement) VALUES (1, 'Has Expanded Package', 'HasExpandedPackage = 1')
INSERT INTO @Items (ID, DisplayName, SQLStatement) VALUES (2, 'Has HBO Package', 'HasHBOPackage = 1')

SELECT * FROM @Items

DECLARE @City VARCHAR(200)
DECLARE @State VARCHAR(2)
DECLARE @ZipCode VARCHAR(5)
DECLARE @Statement VARCHAR(1000)
DECLARE @SelectedOptions VARCHAR(30)

SET @City = 'Dallas'
SET @State = 'TX'
SET @ZipCode = '12345'
SET @SelectedOptions = '2'

DECLARE @TempOptionsTable TABLE
(
    OptionID VARCHAR(3)
)

DECLARE @Delimiter VARCHAR(1)
DECLARE @StartPosition INT
DECLARE @Length INT
DECLARE @Item VARCHAR(3)

SET @Delimiter = ','

WHILE LEN(@SelectedOptions) > 0
BEGIN
    SET @StartPosition = CHARINDEX(@Delimiter, @SelectedOptions)

    IF @StartPosition < 0
    BEGIN
     SET @StartPosition = 0
    END

    SET @Length = LEN(@SelectedOptions) - @StartPosition - 1

    IF @Length < 0
    BEGIN
     SET @Length = 0
    END

    IF @StartPosition > 0
    BEGIN
     SET @Item = SUBSTRING(@SelectedOptions, 1, @StartPosition - 1)
     SET @SelectedOptions = SUBSTRING(@SelectedOptions, @StartPosition + 1, LEN(@SelectedOptions) - @StartPosition)
    END
    ELSE
    BEGIN
     SET @Item = @SelectedOptions
     SET @SelectedOptions = ''
    END

    INSERT INTO @TempOptionsTable (OptionID) VALUES (@Item)
END

DECLARE @StatementTable TABLE
(
    StatementID INT IDENTITY,
    OptionID INT,
    SQLStatement VARCHAR(200)
)

SELECT * FROM @StatementTable

--SELECT I.* FROM @TempOptionsTable TOT INNER JOIN @Items I ON I.ID = TOT.OptionID

INSERT INTO @StatementTable
SELECT I.ID, I.SQLStatement FROM @TempOptionsTable TOT INNER JOIN @Items I ON I.ID = TOT.OptionID

SELECT * FROM @StatementTable

DECLARE @Iterator INT
DECLARE @MaxIndex INT
DECLARE @TempStatement VARCHAR(200)

SELECT @Iterator = MIN(StatementID) FROM @StatementTable
SELECT @MaxIndex = MAX(StatementID) FROM @StatementTable

SELECT @Iterator, @MaxIndex

SELECT @Statement = 'SELECT * FROM Users WHERE City = ''' + @City + ''' AND State = ''' + @State + ''' AND ZipCode = ''' + @ZipCode + ''''

SELECT @Statement

WHILE @Iterator < (@MaxIndex + 1)
BEGIN
    SELECT @TempStatement = SQLStatement FROM @StatementTable WHERE StatementID = @Iterator

    SET @Statement = @Statement + ' AND ' + @TempStatement

    SET @Iterator = @Iterator + 1
END

SELECT @Statement

--EXEC(@Statement)

This way, all you have to do is add a new record to your statement table and you instantly get the data binding and extra search features. You need to make sure the city state and zip code fields are properly cleansed though, as they could be potentially exploited to SQL injection if those are open text fields (and not drop downs or something).