The demo database for this article is Northgale, which is an inflated version of the old demo database Northwind from Microsoft. Revision History The Northgale Demo Database Implementing search_orders with a Parameterised Query Then follows the two main chapters, one devoted to static SQL and one to dynamic SQL. The second chapter introduces the task in focus for the rest of the article: the requirement to implement the routine search_orders in the Northgale database, the demo database for this article. The first looks at some methods which are good for very simple cases where you only need to handle a very small set of choices and where the more general methods shoot over the target. The article starts with two short chapters. Note that if you still are on SQL 2008 you should be on the last service pack, that is, SP4 for SQL 2008 and SP3 for SQL 2008 R2. This article assumes that you are on SQL 2008 or later. They are both viable, and as a good SQL programmer you should have both of them in your toolbox since both have their strengths and weaknesses. We will look at both these approaches in this article. Or you can use dynamic SQL to build a query string which includes only the search conditions the user specified. You can write a static SQL query and add the hint OPTION (RECOMPILE) which forces SQL Server to compile the query every time. Rather, you want the query plan to be different depending on user input. When it comes to the latter, there is a key theme: there is no single execution plan that is good for all possible search criterias. When you implement such a function with SQL Server there are two challenges: to produce the correct result and have good performance. It is very common in information systems to have functions where the users are able to search the data by selectingįreely among many possible search fields. See here for font conventions used in this article. Dynamic Search Conditions in T-SQL Dynamic Search Conditions in T‑SQLĪn SQL text by Erland Sommarskog, SQL ServerĬopyright applies to this text.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |