Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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

2 Solutions
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now