Conclusion


You now know how to gather user input and format it to build a query dynamically. You're aware of some of the pitfalls in constructing dynamic queries as well as some of the security risks.

Keep your database secure by constructing parameterized queries and executing them through the sp_executeSql stored procedure. You can even use these techniques to accomplish tasks beyond simply returning data to the user.

Chapter 7: Quick Reference

To

Do this

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

Right-click a table in the Object Explorer, then choose Open Table from the context menu. Use the buttons on the Query Designer toolbar and the related panes to design your query.

Obtain a list of tables and views in a database

Execute the SQL statement

[View full width]

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPEFROM INFORMATION_ SCHEMA.TABLES


Create queries dynamically

Concatenate the SELECT keyword followed by a list of column names and then the FROM keyword followed by a table name.

Sort the results

Add an ORDER BY clause followed by a list of column names. Add the DESC statement to arrange the information in descending order.

Filter the results

Add a WHERE clause (before the ORDER BY clause, if there is one), followed by the filter conditions.

Prevent SQL-injection attacks

Always use parameterized queries. Try to perform all the operations under an appropriate security context.

Enhance dynamic query execution

Use sp_executeSql to persist the query plan in the cache.





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

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