A User Interface for Building Queries

SQL Server Management Studio includes a sophisticated user interface for building queries. Let's take a quick tour of this query-building interface to give you an idea of how dynamic queries can be built. Your application won't need all the bells and whistles that SQL Server Management Studio provides. In fact, you will want to consider carefully how best to limit the choices your users can make.

Build a Query in SQL Server Management Studio's Query Builder


In SQL Server Management Studio, attach the AdventureWorks database if necessary, then expand the database in Object Explorer, as shown here:


Chapter 4, "Transferring Your Database to Other Systems," explains how to attach a database.


Expand the Tables node in the Object Explorer tree.


Scroll to find the Sales.Customer table. Right-click the table and choose Open Table from the context menu. The table will appear, as shown below. (The Properties window has been closed in this screenshot.)


SQL Server Management Studio adds the Query Designer toolbar, shown below. The first three buttons on this toolbar display the tables for your query, the list of selected columns, and the actual SQL your query has generated, respectively.


Click the first (Show Diagram Pane) button, then select the CustomerID and AccountNumber columns.


Click the second (Show Criteria Pane) button. Uncheck the Output checkbox in the * row. Click in the Sort Type column on the CustomerID row and select Ascending from the dropdown list.


Scroll to the right if necessary to see the Filter column. Again, on the CustomerID row, enter <10 and press the Enter key.


Click the third (Show SQL Pane) button. Your screen will look similar to the following:


Notice the SQL query string that SQL Server Management Studio has generated for you in the SQL pane. Click the Execute SQL toolbar button. Nine records will be retrieved in your Results pane.

Your application's users will typically have nothing to do with the query string. They don't know anything about SQL. It's your job to create the query string correctly, either at design time or through your application's code at runtime. Constructing the query string dynamically at runtime allows you to base the query string on user input.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net