Chapter 7. Building Queries Dynamically

After completing this chapter, you will be able to:

• Build a query using SQL Server Management Studio's Query Builder

• Retrieve information about a database from the database's system tables

• Create simple queries dynamically, based on user input

• Format user input to sort and filter sophisticated dynamic queries

• Parse and reformat dates for use in a filter

• Protect your database from a "SQL-injection" attack

• Use sp_executeSql to submit a query

In the previous chapter, you learned how to increase the performance of your queries. Now you know how to create an efficient set of queries to give your users the most important information from your application using predefined queries in stored procedures or views.

However, in all but the simplest applications, there's no way to figure out ahead of time all the possible variations on what information your users will need or how they'll want it filtered and organized. Rather than try to plan for every possibility, you can give the users control over the information the application provides. This chapter will show you how to build queries dynamically based on choices that the user makes at runtime.