Multiple Filterin with SQL Stored Procedure


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

Who is Participating?
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

FROM yourTable
WHERE products = ISNULL(@products, products)
  AND services = ISNULL(@services, services)
  AND education = ISNULL(@education, @education)

Open in new window

HainKurtSr. System AnalystCommented:
and when calling the above sp posted by tigin44, do not set parameters if the values are ""
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)

karmatiAuthor Commented:
Thanks a lot
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.