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
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.