Multiple Filterin with SQL Stored Procedure

Posted on 2010-01-07
Last Modified: 2012-05-08

This is a fire safety community portal in Czech Republic done with ASP.NET in C# and the public will be doing search from the database of fire safety companies.

There is three filtering criteria due to the offering of the companies as: Products, Services and Training. When I am filtering the results, it only filters according to my last selection.

How can I do multiple filtering with MSSQL Stored Procedure so that for example if a visitor searches for a firm that offer products AND services, it is rendered from the database as such. What is the SQL code for such a procedure?

Many Thanks,
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="NarrowResults.aspx.cs" Inherits="CreateAccount2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<head runat="server">
    <form id="form1" runat="server">
    <asp:CheckBoxList ID="CheckBoxList4" runat="server">
            <br />
    <asp:CheckBoxList ID="CheckBoxList5" runat="server">
            <br />
        <asp:CheckBoxList ID="CheckBoxList6" runat="server">

Open in new window

Question by:karmati
    LVL 26

    Accepted Solution

    you can create procedure like the one attached. Here if you supply a parameter then the corresponding values are listed. If you do not suply any paramater or supply parameters as NULL then all the values listed. If you dont want a group value to be listed then supply a value that is not in the list.
    create procedure spname(
    	@products	varchar(100) = null,
    	@services	varchar(100) = null,
    	@education	varchar(100) = null
    SELECT *
    FROM yourTable
    WHERE products = ISNULL(@products, products)
      AND services = ISNULL(@services, services)
      AND education = ISNULL(@education, @education)

    Open in new window

    LVL 51

    Expert Comment

    and when calling the above sp posted by tigin44, do not set parameters if the values are ""
    LVL 4

    Assisted Solution

    and the last line should be

     AND education = ISNULL(@education, education)

    instead of

     AND education = ISNULL(@education, @education)

    if you need todo partial searches then append %: (right partial search)

    WHERE products like ISNULL(@products + '%', products)
      AND services like ISNULL(@services + '%', services)
      AND education like ISNULL(@education + '%', education)


    Author Closing Comment

    Thanks a lot

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now