StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The problem is the open list nature of the argument. I can be <code>Joh</code>, it can be <code>Joh Do</code>, it can be <code>Joh Do Na</code> and so on and so forth. You have two main alternatives:</p> <ul> <li>parse the input in the web app (in ASP I assume) and then call an appropriate procedure for the number of entries (ie. <code>exec usp_findSuggestions1 'Joh'</code>, <code>exec usp_findSuggestions2 'Joh', 'Do'</code>, <code>exec usp_findSuggestions1 'Joh', 'Do', 'Na'</code>). The first procedure uses 1 <code>contains</code>, the second has 2 <code>contains .. and contains ...</code> and the last has 3. This may look totally ugly from a DRY, code design and specially code maintenance pov, but is actually the best solution as far as T-SQL is concerned, due primarily to the plan stability of these queries.</li> <li>pass the input straight into a single stored procedure, where you can split it into components and build a dynamic T-SQL query with as many <code>contains</code> as necessary.</li> </ul> <p>Both solutions are imperfect. Ultimately, you have two problems, and both have been investigated before to quite some depth:</p> <ul> <li>the problem of passing a list to a T-SQL procedure. See <a href="http://www.sommarskog.se/arrays-in-sql-2005.html" rel="nofollow noreferrer">Arrays and Lists in SQL Server 2005 and Beyond</a></li> <li>the problem of an undetermined number of conditions in the WHERE clause, see <a href="http://www.sommarskog.se/dynamic_sql.html" rel="nofollow noreferrer">The Curse and Blessings of Dynamic SQL</a></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