Okay, let me restate my understanding of your question: You want a stored procedure that can take a variable number of parameters and pass back the top row that matches the parameters in the weighted order of preference passed on SQL Server 2005.
Ideally, it will use WHERE clauses to prevent full tables scans plus take advantage of indices and will "short circuit" the search - you don't want to search all possible combinations if one can be found early. Perhaps we can also allow other comparators than = such as >= for dates, LIKE for strings, etc.
One possible way is to pass the parameters as XML like in this article and use .Net stored procedures but let's keep it plain vanilla T-SQL for now.
This looks to me like a binary search on the parameters: Search all parameters, then drop the last one, then drop the second last one but include the last one, etc.
Let's pass the parameters as a delimited string since stored procedures don't allow for arrays to be passed as parameters. This will allow us to get a variable number of parameters in to our stored procedure without requiring a stored procedure for each variation of parameters.
In order to allow any sort of comparison, we'll pass the entire WHERE clause list, like so: title like '%something%'
Passing multiple parameters means delimiting them in a string. We'll use the tilde ~ character to delimit the parameters, like this: author = 'Chris Latta'~title like '%something%'~pages >= 100
Then it is simply a matter of doing a binary weighted search for the first row that meets our ordered list of parameters (hopefully the stored procedure with comments is self-explanatory but if not, let me know). Note that you are always guaranteed a result (assuming your table has at least one row) as the last search is parameterless.
Here is the stored procedure code:
CREATE PROCEDURE FirstMatch
@SearchParams VARCHAR(2000)
AS
BEGIN
DECLARE @SQLstmt NVARCHAR(2000)
DECLARE @WhereClause NVARCHAR(2000)
DECLARE @OrderByClause NVARCHAR(500)
DECLARE @NumParams INT
DECLARE @Pos INT
DECLARE @BinarySearch INT
DECLARE @Rows INT
-- Create a temporary table to store our parameters
CREATE TABLE #params
(
BitMask int, -- Uniquely identifying bit mask
FieldName VARCHAR(100), -- The field name for use in the ORDER BY clause
WhereClause VARCHAR(100) -- The bit to use in the WHERE clause
)
-- Temporary table identical to our result set (the books table) so intermediate results arent output
CREATE TABLE #junk
(
id INT,
author VARCHAR(50),
title VARCHAR(50),
printed DATETIME,
pages INT
)
-- Ill use tilde ~ as the delimiter that separates parameters
SET @SearchParams = LTRIM(RTRIM(@SearchParams))+ '~'
SET @Pos = CHARINDEX('~', @SearchParams, 1)
SET @NumParams = 0
-- Populate the #params table with the delimited parameters passed
IF REPLACE(@SearchParams, '~', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @NumParams = @NumParams + 1
SET @WhereClause = LTRIM(RTRIM(LEFT(@SearchParams, @Pos - 1)))
IF @WhereClause <> ''
BEGIN
-- This assumes your field names dont have spaces and that you leave a space between the field name and the comparator
INSERT INTO #params (BitMask, FieldName, WhereClause) VALUES (POWER(2, @NumParams - 1), LTRIM(RTRIM(LEFT(@WhereClause, CHARINDEX(' ', @WhereClause, 1) - 1))), @WhereClause)
END
SET @SearchParams = RIGHT(@SearchParams, LEN(@SearchParams) - @Pos)
SET @Pos = CHARINDEX('~', @SearchParams, 1)
END
END
-- Set the binary search to search from all parameters down to one in order of preference
SET @BinarySearch = POWER(2, @NumParams)
SET @Rows = 0
WHILE (@BinarySearch > 0) AND (@Rows = 0)
BEGIN
SET @BinarySearch = @BinarySearch - 1
SET @WhereClause = ' WHERE '
SET @OrderByClause = ' ORDER BY '
SELECT @OrderByClause = @OrderByClause + FieldName + ', ' FROM #params WHERE (@BinarySearch & BitMask) = BitMask ORDER BY BitMask
SET @OrderByClause = LEFT(@OrderByClause, LEN(@OrderByClause) - 1) -- Remove the trailing comma
SELECT @WhereClause = @WhereClause + WhereClause + ' AND ' FROM #params WHERE (@BinarySearch & BitMask) = BitMask ORDER BY BitMask
SET @WhereClause = LEFT(@WhereClause, LEN(@WhereClause) - 4) -- Remove the trailing AND
IF @BinarySearch = 0
BEGIN
-- If nothing found so far, return the top row in the order of the parameters fields
SET @WhereClause = ''
-- Use the full order sequence of fields to return the results
SET @OrderByClause = ' ORDER BY '
SELECT @OrderByClause = @OrderByClause + FieldName + ', ' FROM #params ORDER BY BitMask
SET @OrderByClause = LEFT(@OrderByClause, LEN(@OrderByClause) - 1) -- Remove the trailing comma
END
-- Find out if there are any results for this search
SET @SQLstmt = 'SELECT TOP 1 id, author, title, printed, pages INTO #junk FROM books' + @WhereClause + @OrderByClause
Exec (@SQLstmt)
SET @Rows = @@RowCount
END
-- Stop the result set being eaten by the junk table
SET @SQLstmt = REPLACE(@SQLstmt, 'INTO #junk ', '')
-- Uncomment the next line to see the SQL you are producing
--PRINT @SQLstmt
-- This gives the result set
Exec (@SQLstmt)
END
This stored procedure is called like so:
FirstMatch 'author = ''Chris Latta''~pages > 100~title like ''%something%'''
There you have it - a fully expandable, optimised search for the top result in weighted order of preference. This was an interesting problem and shows just what you can pull off with native T-SQL.
A couple of small issues with this:
- it relies on the caller to know that they must leave a space after the field name for the parameter to work properly
- you can't have field names with spaces in them - fixable with some effort
- it assumes that the relevant sort order is always ascending
- the next programmer that has to look at this procedure will think you're insane :)