SQL Server 2008 - Keyword search using table Join
- by Aaron Wagner
Ok, I created a Stored Procedure that, among other things, is searching 5 columns for a particular keyword. To accomplish this, I have the keywords parameter being split out by a function and returned as a table. Then I do a Left Join on that table, using a LIKE constraint.
So, I had this working beautifully, and then all of the sudden it stops working. Now it is returning every row, instead of just the rows it needs.
The other caveat, is that if the keyword parameter is empty, it should ignore it.
Given what's below, is there A) a glaring mistake, or B) a more efficient way to approach this?
Here is what I have currently:
ALTER PROCEDURE [dbo].[usp_getOppsPaged]
@startRowIndex int,
@maximumRows int,
@city varchar(100) = NULL,
@state char(2) = NULL,
@zip varchar(10) = NULL,
@classification varchar(15) = NULL,
@startDateMin date = NULL,
@startDateMax date = NULL,
@endDateMin date = NULL,
@endDateMax date = NULL,
@keywords varchar(400) = NULL
AS
BEGIN
SET NOCOUNT ON;
;WITH Results_CTE AS
(
SELECT opportunities.*,
organizations.*,
departments.dept_name,
departments.dept_address,
departments.dept_building_name,
departments.dept_suite_num,
departments.dept_city,
departments.dept_state,
departments.dept_zip,
departments.dept_international_address,
departments.dept_phone,
departments.dept_website,
departments.dept_gen_list,
ROW_NUMBER() OVER (ORDER BY opp_id) AS RowNum
FROM opportunities
JOIN departments ON opportunities.dept_id = departments.dept_id
JOIN organizations ON departments.org_id=organizations.org_id
LEFT JOIN Split(',',@keywords) AS kw ON
(title LIKE '%'+kw.s+'%' OR
[description] LIKE '%'+kw.s+'%' OR
tasks LIKE '%'+kw.s+'%' OR
requirements LIKE '%'+kw.s+'%' OR
comments LIKE '%'+kw.s+'%')
WHERE
(
(@city IS NOT NULL AND (city LIKE '%'+@city+'%' OR dept_city LIKE '%'+@city+'%' OR org_city LIKE '%'+@city+'%'))
OR
(@state IS NOT NULL AND ([state] = @state OR dept_state = @state OR org_state = @state))
OR
(@zip IS NOT NULL AND (zip = @zip OR dept_zip = @zip OR org_zip = @zip))
OR
(@classification IS NOT NULL AND (classification LIKE '%'+@classification+'%'))
OR
((@startDateMin IS NOT NULL AND @startDateMax IS NOT NULL) AND ([start_date] BETWEEN @startDateMin AND @startDateMax))
OR
((@endDateMin IS NOT NULL AND @endDateMax IS NOT NULL) AND ([end_date] BETWEEN @endDateMin AND @endDateMax))
OR
(
(@city IS NULL AND
@state IS NULL AND
@zip IS NULL AND
@classification IS NULL AND
@startDateMin IS NULL AND
@startDateMax IS NULL AND
@endDateMin IS NULL AND
@endDateMin IS NULL)
)
)
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @startRowIndex
AND RowNum < @startRowIndex + @maximumRows;
END
