Filters using stored procedures and XML
Sometimes when a user get a dataresult on a webpage, you want to give him the possibility
to set some filters. (fe. using some comboboxes which are populated from small definition tables, ex. tblCountrys, of tblTypes.)
For security and performance reasons we want to use stored procedures and no dynamically created sql string.
This small sample demonstrates a filter technique using XML to set some filters.
So in the GUI, you just have to make an XML string and give it as a parameter. It's is very easy to extend.
The sample uses the database pubs. In this case we are using a simple select statement.
CREATE PROCEDURE usp_GetFilteredPubInfo
@Filters varchar(2048) = null
AS
DECLARE @tab_Filters Table (FIELDNAME varchar(50), FIELDVALUE varchar(50))
DECLARE @idoc int
-- Parse the xml data if present
IF not @Filters is null
BEGIN
EXEC sp_xml_preparedocument @idoc OUTPUT, @Filters
INSERT INTO @tab_Filters
SELECT *
FROM OPENXML (@idoc, '/FILTERS/FILTER',1)
WITH (FIELDNAME varchar(50), FIELDVALUE varchar(50))
END
DECLARE @CountPubNames int
DECLARE @CountTypes int
DECLARE @CountCity int
-- Count the amount of filters
SELECT @CountPubNames = COUNT(FIELDVALUE) FROM @tab_Filters WHERE FIELDNAME = 'PubName'
SELECT @CountTypes = COUNT(FIELDVALUE) FROM @tab_Filters WHERE FIELDNAME = 'Type'
SELECT @CountCity = COUNT(FIELDVALUE) FROM @tab_Filters WHERE FIELDNAME = 'City'
-- Your select instruction and for each filter, you test if the count is zero. if there are values, you use them in your subquery.
-- In most of the cases, you will
select title, type, pub_name, city, state
from titles, publishers
where titles.pub_ID = publishers.pub_id
and (@CountPubNames=0 OR (pub_name in (SELECT FIELDVALUE FROM @tab_Filters WHERE FIELDNAME = 'PubName')))
and (@CountTypes=0 OR (type in (SELECT FIELDVALUE FROM @tab_FIlters WHERE FIELDNAME = 'Type')))
and (@CountCity=0 OR (city in (SELECT FIELDVALUE FROM @tab_FIlters WHERE FIELDNAME = 'City')))
GO
Execute the following:
exec usp_GetFilteredPubInfo
exec usp_GetFilteredPubInfo '<FILTERS><FILTER FIELDNAME="Type" FIELDVALUE="business"/><FILTER FIELDNAME="City" FIELDVALUE="Berkeley"/></FILTERS>'
- Ken