March 5, 2024 at 10:38 pm
Hello all,
I have defined a set of filters and assigned each set a FilterID. I want to pass this FilterID to a stored procedure and filter the results accordingly. My working example inserts the broadest set of data into a temp table and runs conditional deletes to get the appropriate results.
Before I delve into dynamic sql I'd like to explore whether its possible to do this in a single select statement and eliminate the conditional deletes. if it is possible I may look at passing FilterID to an inline table-valued function (iTVF).
DROP TABLE IF EXISTS SampleData
CREATE TABLE SampleData (City VARCHAR(16) NOT NULL,State VARCHAR(2) NOT NULL,CityType VARCHAR(6) NOT NULL)
INSERT INTO SampleData (City,State,CityType)
VALUES
('Bisbee','AZ','Small' ),
('Carefree','AZ','Small' ),
('Gila Bend','AZ','Small' ),
('Flagstaff','AZ','Medium' ),
('Yuma','AZ','Medium' ),
('Lake Havasu City','AZ','Small' ),
('Casa Grande','AZ','Small' ),
('Phoenix','AZ','Large' ),
('Tucson','AZ','Large' ),
('Antonito', 'CO','Small' ),
('Alamosa', 'CO','Small' ),
('Moffat', 'CO','Small' ),
('Thornton', 'CO','Medium' ),
('Arvada', 'CO','Medium' ),
('Crestone','CO','Small' ),
('South Fork','CO','Small' ),
('Denver','CO','Large' ),
('Colorado Springs', 'CO', 'Large' )
DROP TABLE IF EXISTS Filter
CREATE TABLE Filter (FilterID INT NULL,FilterAttribute VARCHAR(20) NULL,FilterValue VARCHAR(20) NULL)
--Filter rules
--CityType(s) is required
--State is optional
--CityInclusion takes precedence over CityExclusion
INSERT INTO Filter (FilterID,FilterAttribute,FilterValue)
VALUES
--Select all small and large cities
(1,'CityType','Small'),
(1,'CityType','Large'),
--Select all large AZ cities
(2,'CityType','Large'),
(2,'State','AZ'),
--Select only Alamosa
(3,'CityType','Small'),
(3,'CityType','Large'),
(3,'CityType','Medium'),
(3,'CityInclusion','Alamosa'),
--Select all CO cities except Denver
(4,'CityType','Small'),
(4,'CityType','Large'),
(4,'CityType','Medium'),
(4,'State','CO'),
(4,'CityExclusion','Denver')
--SELECT * FROM SampleData
--SELECT * FROM Filter
GO
CREATE OR ALTER PROCEDURE SampleDataProcedure @FilterID INT
AS
DROP TABLE IF EXISTS #Filter
SELECT
*
INTO #Filter
FROM Filter
WHERE
FilterID = @FilterID
DROP TABLE IF EXISTS #SampleData
SELECT * INTO #SampleData FROM SampleData SD
WHERE
CityType IN (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityType')
--Here is where I would like to expand the where clause if possible instead of the following conditional delete statements
IF EXISTS (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'State')
DELETE FROM #SampleData WHERE State NOT IN (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'State')
IF EXISTS (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityInclusion')
DELETE FROM #SampleData WHERE City NOT IN (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityInclusion')
IF NOT EXISTS (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityInclusion') AND EXISTS (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityExclusion')
DELETE FROM #SampleData WHERE City IN (SELECT FilterValue FROM #Filter WHERE FilterAttribute = 'CityExclusion')
SELECT * FROM #SampleData
GO
EXEC SampleDataProcedure 1
EXEC SampleDataProcedure 2
EXEC SampleDataProcedure 3
EXEC SampleDataProcedure 4
March 6, 2024 at 1:01 pm
I would look at dynamic sql as well.
DECLARE @FilterId int = 4;
WITH CityTypes
AS
(
SELECT City, [State], CityType
FROM dbo.SampleData S
WHERE EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityType'
AND F.FilterValue = S.CityType
)
)
,States
AS
(
SELECT City, [State], CityType
FROM CityTypes T
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'State'
)
OR EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'State'
AND F.FilterValue = T.[State]
)
)
,CityInclusions
AS
(
SELECT City, [State], CityType
FROM States S
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityInclusion'
)
OR EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityInclusion'
AND F.FilterValue = S.City
)
)
SELECT City, [State], CityType
FROM CityInclusions I
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityExclusion'
)
OR NOT EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityExclusion'
AND F.FilterValue = I.City
AND NOT EXISTS
(
SELECT 1
FROM dbo.[Filter] F
WHERE F.FilterID = @FilterId
AND F.FilterAttribute = 'CityInclusion'
AND F.FilterValue = I.City
)
);
March 6, 2024 at 3:55 pm
I've done similar set ups. Dynamic SQL is definitely a good choice here. To keep the FilterAttribute column consistent, I suggest you use a different column to control include or exclude. For example:
CREATE TABLE Filter (FilterID INT NOT NULL CONSTRAINT Filter__PK PRIMARY KEY CLUSTERED,
FilterAttribute VARCHAR(50) NULL, FilterValue VARCHAR(50) NULL,
IncludeOrExclude CHAR(1) NOT NULL CONSTRAINT Filter__DF_IncludeOrExclude DEFAULT 'I',
CONSTRAINT Filter__CK_IncludeOrExclude CHECK (IncludeOrExclude IN ('E', 'I')))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply