complicated AND/OR search based on user criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

complicated AND/OR search based on user criteria

Author

Topic

rachricketts
Starting Member

3 Posts

Posted - 2005-03-08 : 09:59:55

I have a stored procedure which selects results from a table based on keyword matches and a couple of other parameters passed from the user and dumps them into a temp table.

A secondary series of selections is then made from the temp table based on further criteria and each selection in the series is dumped into a second temp table effectively building up a data set based on a series of 'OR' criteria.

The duplicate results are then removed from the second temp table.

What I need to do now is allow users to pass even more parameters filled with 'AND' and 'OR' values so that they can build up the secondary selections using the exact criteria they have chosen.

I have a complete mind block on how to achieve this and would really appreciate some help in the right direction.

The stored procedure looks like this at the moment:

CREATE PROCEDURE [dbo].mainContentSearchAll
@SearchTerm nvarchar(1000) = NULL,
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@SearchArea varchar(50) = NULL,
@SearchTL varchar(50) = NULL,
@SearchTD varchar(50) = NULL,
@SearchTLi varchar(50) = NULL,
@SearchRT varchar(50) = NULL,
@SearchTA varchar(50) = NULL,
@SearchOS varchar(50) = NULL,
@SearchDL varchar(50) = NULL,
@SearchDT varchar(50) = NULL
AS

-- Create an initial result set ready to filter by search wizard params if neccessary
CREATE TABLE #InitialResults
(
pageID int,
subSectionID smallint,
pageTitle varchar(150),
pageDateTime datetime,
pageAuthor varchar(150),
bodyCopy text,
keywords varchar(150),
rank int
)

IF @SearchTerm IS NULL
BEGIN
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate) AND mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NULL AND @EndDate IS NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate)
END
ELSE
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
END
END
--IF @SearchTerm IS NOT NULL
ELSE
BEGIN
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate) AND mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NULL AND @EndDate IS NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate)
END
ELSE
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
END
END

-- Create a second result set based on 'OR' search wizard params
CREATE TABLE #SecondResults
(
[id] [int] IDENTITY (1, 1) NOT NULL,
pageID int,
subSectionID smallint,
pageTitle varchar(150),
pageDateTime datetime,
pageAuthor varchar(150),
bodyCopy text,
keywords varchar(150),
rank int
)

IF @searchTL IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((((#InitialResults INNER JOIN mainContentTechnicalLevel ON #InitialResults.pageID = mainContentTechnicalLevel.pageID)
INNER JOIN technicalLevel ON mainContentTechnicalLevel.technicalLevelID = technicalLevel.technicalLevelID) INNER JOIN mainContentOSversion ON #InitialResults.pageID = mainContentOSversion.pageID)
INNER JOIN OSVersion ON mainContentOSversion.OSversionID = OSVersion.OSversionID)
WHERE mainContentTechnicalLevel.technicalLevelID IN (SELECT Value FROM dbo.Split(@searchTL,':')) AND mainContentTechnicalLevel.pageID = #InitialResults.pageID
AND mainContentOSversion.OSversionID IN (SELECT Value FROM dbo.Split(@searchOS,':')) AND mainContentOSversion.pageID = #InitialResults.pageID
END
IF @searchTD IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentTargetDevice ON #InitialResults.pageID = mainContentTargetDevice.pageID)
INNER JOIN targetDevice ON mainContentTargetDevice.targetDeviceID = targetDevice.targetDeviceID)
WHERE mainContentTargetDevice.targetDeviceID IN (SELECT Value FROM dbo.Split(@searchTD,':')) AND mainContentTargetDevice.pageID = #InitialResults.pageID
END
IF @searchTLi IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentTechnology ON #InitialResults.pageID = mainContentTechnology.pageID)
INNER JOIN technology ON mainContentTechnology.technologyID = technology.technologyID)
WHERE mainContentTechnology.technologyID IN (SELECT Value FROM dbo.Split(@searchTLi,':')) AND mainContentTechnology.pageID = #InitialResults.pageID
END
IF @searchRT IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentResourceType ON #InitialResults.pageID = mainContentResourceType.pageID)
INNER JOIN resourceType ON mainContentResourceType.resourceTypeID = resourceType.resourceTypeID)
WHERE mainContentResourceType.resourceTypeID IN (SELECT Value FROM dbo.Split(@searchRT,':')) AND mainContentResourceType.pageID = #InitialResults.pageID
END
IF @searchTA IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentTargetAudience ON #InitialResults.pageID = mainContentTargetAudience.pageID)
INNER JOIN targetAudience ON mainContentTargetAudience.targetAudienceID = targetAudience.targetAudienceID)
WHERE mainContentTargetAudience.targetAudienceID IN (SELECT Value FROM dbo.Split(@searchTA,':')) AND mainContentTargetAudience.pageID = #InitialResults.pageID
END
IF @searchOS IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentOSversion ON #InitialResults.pageID = mainContentOSversion.pageID)
INNER JOIN OSVersion ON mainContentOSversion.OSversionID = OSVersion.OSversionID)
WHERE mainContentOSversion.OSversionID IN (SELECT Value FROM dbo.Split(@searchOS,':')) AND mainContentOSversion.pageID = #InitialResults.pageID
--DELETE FROM #SecondResults WHERE #SecondResults.pageID <> #ThirdResults.pageID
END
IF @searchDL IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentDevelopmentLanguage ON #InitialResults.pageID = mainContentDevelopmentLanguage.pageID)
INNER JOIN developmentLanguage ON mainContentDevelopmentLanguage.developmentLanguageID = developmentLanguage.developmentLanguageID)
WHERE mainContentDevelopmentLanguage.developmentLanguageID IN (SELECT Value FROM dbo.Split(@searchDL,':')) AND mainContentDevelopmentLanguage.pageID = #InitialResults.pageID
END
IF @searchDT IS NOT NULL
BEGIN
INSERT INTO #SecondResults
SELECT #InitialResults.*
FROM ((#InitialResults INNER JOIN mainContentDeveloperToolVersion ON #InitialResults.pageID = mainContentDeveloperToolVersion.pageID)
INNER JOIN developerToolVersion ON mainContentDeveloperToolVersion.developerToolVersionID = developerToolVersion.developerToolVersionID)
WHERE mainContentDeveloperToolVersion.developerToolVersionID IN (SELECT Value FROM dbo.Split(@searchDT,':')) AND mainContentDeveloperToolVersion.pageID = #InitialResults.pageID
END

IF @searchTL IS NULL AND @searchTD IS NULL AND @searchTLi IS NULL AND @searchRT IS NULL AND @searchTA IS NULL AND @searchOS IS NULL AND @searchDL IS NULL AND @searchDT IS NULL
BEGIN
INSERT INTO #SecondResults
SELECT * FROM #InitialResults
END

DELETE FROM #SecondResults
WHERE EXISTS (SELECT NULL FROM #SecondResults b WHERE b.[pageID] = #SecondResults.[pageID] GROUP BY b.[pageID] HAVING #SecondResults.[id] < MAX(b.[id]))

SELECT * FROM #SecondResults

--Drop temp table
DROP TABLE #InitialResults
DROP TABLE #SecondResults
GO

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-08 : 10:10:17

The usual method that I would use for adding optional criteria is a WHERE statement formed similar to this

AND (@parameter IS NULL OR (columnvalue = @parameter))

-------
Moo. :)

rachricketts
Starting Member

3 Posts

Posted - 2005-03-08 : 10:49:44

I could build up a simple WHERE AND/OR statement like that if the AND/OR bits weren't passed as parameters. I wonder if I should build the statement as dynamic SQL and then execute it. I have been reading about how dynamic SQL is a security risk though.

I might have over complicated the SQL in my stored procedure. Is there a simpler way to do what I'm trying to do?

rachricketts
Starting Member

3 Posts

Posted - 2005-03-10 : 10:41:31

If anyone is interested, I have a working solution which defaults to a multiple 'OR' selection unless the user puts some 'AND's in there. I looped through my initial results set checking for how many times each result matched the second lot of criteria. If the user wanted an 'AND' selection but the result didn't match every time then the record gets deleted from the initial results set. If the user wanted an 'OR' selection but the result didn't even match once then the record gets deleted from the initial results set.

It looks like this:

CREATE PROCEDURE [dbo].mainContentSearchAll
@SearchTerm nvarchar(1000) = NULL,
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@SearchArea varchar(50) = NULL,
@SearchTL varchar(50) = NULL,
@SearchTD varchar(50) = NULL,
@SearchTLi varchar(50) = NULL,
@SearchRT varchar(50) = NULL,
@SearchTA varchar(50) = NULL,
@SearchOS varchar(50) = NULL,
@SearchDL varchar(50) = NULL,
@SearchDT varchar(50) = NULL,
@BoolTL varchar(3) = 'OR',
@BoolTD varchar(3) = 'OR',
@BoolTLi varchar(3) = 'OR',
@BoolRT varchar(3) = 'OR',
@BoolTA varchar(3) = 'OR',
@BoolOS varchar(3) = 'OR',
@BoolDL varchar(3) = 'OR',
@BoolDT varchar(3) = 'OR'
AS

-- Create an initial result set ready to filter by search wizard params if neccessary
CREATE TABLE #InitialResults
(
pageID int,
subSectionID smallint,
pageTitle varchar(150),
pageDateTime datetime,
pageAuthor varchar(150),
bodyCopy text,
keywords varchar(150),
rank int
)

IF @SearchTerm IS NULL
BEGIN
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate) AND mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NULL AND @EndDate IS NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate)
END
ELSE
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, 1000 AS rank
FROM mainContent
END
END
--IF @SearchTerm IS NOT NULL
ELSE
BEGIN
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate) AND mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NULL AND @EndDate IS NULL AND @SearchArea IS NOT NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE mainContent.subSectionID IN (SELECT Value FROM dbo.Split(@searchArea,':'))
END
ELSE IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL AND @SearchArea IS NULL
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
WHERE (pageDateTime BETWEEN @StartDate AND @EndDate)
END
ELSE
BEGIN
INSERT INTO #InitialResults
SELECT mainContent.pageID, mainContent.subSectionID, mainContent.pageTitle, mainContent.pageDateTime, mainContent.pageAuthor, mainContent.bodyCopy, mainContent.keywords, KEY_TBL.RANK AS rank
FROM mainContent INNER JOIN
CONTAINSTABLE (mainContent, *, @searchTerm) AS KEY_TBL
ON mainContent.pageID = KEY_TBL.[KEY]
END
END

CREATE TABLE #SearchParamResults
(
[id] [int] IDENTITY (1, 1) NOT NULL,
pageID int
)

DECLARE @countParam int
DECLARE @countSearchParam int
DECLARE @iCount int
DECLARE @searchParamResultsCount int

INSERT INTO #SearchParamResults SELECT DISTINCT #InitialResults.pageID FROM #InitialResults
SELECT @searchParamResultsCount = count(*) FROM #SearchParamResults

IF @searchTD IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchTD,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentTargetDevice
WHERE mainContentTargetDevice.targetDeviceID IN (SELECT Value FROM dbo.Split(@searchTD,':'))
AND mainContentTargetDevice.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTD = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTD = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchTL IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchTL,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentTechnicalLevel
WHERE mainContentTechnicalLevel.technicalLevelID IN (SELECT Value FROM dbo.Split(@searchTL,':'))
AND mainContentTechnicalLevel.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchTLi IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchTLi,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentTechnology
WHERE mainContentTechnology.technologyID IN (SELECT Value FROM dbo.Split(@searchTLi,':'))
AND mainContentTechnology.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchRT IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchRT,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentResourceType
WHERE mainContentResourceType.resourceTypeID IN (SELECT Value FROM dbo.Split(@searchRT,':'))
AND mainContentResourceType.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchTA IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchTA,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentTargetAudience
WHERE mainContentTargetAudience.targetAudienceID IN (SELECT Value FROM dbo.Split(@searchTA,':'))
AND mainContentTargetAudience.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchOS IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchOS,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentOSversion
WHERE mainContentOSversion.OSversionID IN (SELECT Value FROM dbo.Split(@searchOS,':'))
AND mainContentOSversion.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchDL IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchDL,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentDevelopmentLanguage
WHERE mainContentDevelopmentLanguage.developmentLanguageID IN (SELECT Value FROM dbo.Split(@searchDL,':'))
AND mainContentDevelopmentLanguage.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END
IF @searchDT IS NOT NULL
BEGIN
SET @iCount = 1
SELECT @countParam = count(*) FROM dbo.Split(@searchDT,':')
WHILE @iCount < @searchParamResultsCount + 1
BEGIN
SELECT @countSearchParam = count(*)
FROM mainContentDeveloperToolVersion
WHERE mainContentDeveloperToolVersion.developerToolVersionID IN (SELECT Value FROM dbo.Split(@searchDT,':'))
AND mainContentDeveloperToolVersion.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
IF @BoolTL = 'AND' AND @countSearchParam < @countParam
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
ELSE IF @BoolTL = 'OR' AND @countSearchParam < 1
BEGIN
DELETE FROM #InitialResults
WHERE #InitialResults.pageID = (SELECT pageID FROM #SearchParamResults WHERE #SearchParamResults.id = @iCount)
END
SET @iCount = @iCount + 1
END
END

SELECT * FROM #InitialResults

DROP TABLE #InitialResults
DROP TABLE #SearchParamResults

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources