StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Precedence Matching
text
Body
copied!<p>I'm trying to do precedence matching on a table within a stored procedure. The requirements are a bit tricky to explain, but hopefully this will make sense. Let's say we have a table called books, with id, author, title, date, and pages fields. </p> <p>We also have a stored procedure that will match a query with ONE row in the table.</p> <p>Here is the proc's signature:</p> <pre><code>create procedure match @pAuthor varchar(100) ,@pTitle varchar(100) ,@pDate varchar(100) ,@pPages varchar(100) as ... </code></pre> <p>The precedence rules are as follows:</p> <ul> <li>First, try and match on all 4 parameters. If we find a match return.</li> <li>Next try to match using any 3 parameters. The 1st parameter has the highest precedence here and the 4th the lowest. If we find any matches return the match.</li> <li>Next we check if any two parameters match and finally if any one matches (still following the parameter order's precedence rules).</li> </ul> <p>I have implemented this case-by-case. Eg: </p> <pre><code> select @lvId = id from books where author = @pAuthor ,title = @pTitle ,date = @pDate ,pages = @pPages if @@rowCount = 1 begin select @lvId return end select @lvId = id from books where author = @pAuthor ,title = @pTitle ,date = @pDate if @@rowCount = 1 begin select @lvId return end .... </code></pre> <p>However, for each new column in the table, the number of individual checks grows by an order of 2. I would really like to generalize this to X number of columns; however, I'm having trouble coming up with a scheme.</p> <p>Thanks for the read, and I can provide any additional information needed.</p> <hr> <p><em>Added:</em></p> <p>Dave and Others, I tried implementing your code and it is choking on the first Order by Clause, where we add all the counts. Its giving me an invalid column name error. When I comment out the total count, and order by just the individual aliases, the proc compiles fine.</p> <p>Anyone have any ideas?</p> <p>This is in Microsoft Sql Server 2005</p>

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