StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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. </p> <p>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.</p> <p>One possible way is to pass the parameters as XML like in <a href="http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx" rel="nofollow noreferrer">this article</a> and use .Net stored procedures but let's keep it plain vanilla T-SQL for now. </p> <p>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.</p> <p>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.</p> <p>In order to allow any sort of comparison, we'll pass the entire WHERE clause list, like so: title like '%something%'</p> <p>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</p> <p>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. </p> <p>Here is the stored procedure code:</p> <pre><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 </code></pre> <p>This stored procedure is called like so:</p> <pre><code>FirstMatch 'author = ''Chris Latta''~pages > 100~title like ''%something%''' </code></pre> <p>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.</p> <p>A couple of small issues with this:</p> <ul> <li>it relies on the caller to know that they must leave a space after the field name for the parameter to work properly</li> <li>you can't have field names with spaces in them - fixable with some effort</li> <li>it assumes that the relevant sort order is always ascending</li> <li>the next programmer that has to look at this procedure will think you're insane :)</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload