Is there an advantage to piecemealing sql strings together vs conditional sql statements in SQL Server itself? I have only about 10 months of SQL experience, so I could be speaking out of pure ignorance here.
Where I work, I see people building entire queries in strings and concatenating strings together depending on conditions. For example:
Set @sql = 'Select column1, column2 from Table 1 '
If SomeCondtion
@sql = @sql + 'where column3 = ' + @param1
else
@sql = @sql + 'where column4 = ' + @param2
That's a real simple example, but what I'm seeing here is multiple joins and huge queries built from strings and then executed. Some of them even write out what's basically a function to execute, including Declare statements, variables, etc. Is there an advantage to doing it this way when you could do it with just conditions in the sql itself? To me, it seems a lot harder to debug, change and even write vs adding cases, if-elses or additional where parameters to branch the query.
Best Answer
Dynamic SQL is used in situations where the conditions aren't known ahead of time. One example I've run into often is an "advanced" search form where the user can search on multiple fields and can search beyond simple "substring" searches where they can specify a search value must match exactly (OR match as a substring, and case sensitivity is an option), and for numeric/date values they can specify operators >, <, >=, <=. It might also happen that one search form is actually searching multiple joined tables. In this case, it's much easier to build a query on the fly as a string and execute it that way, than to try to imagine every possible query they user could submit and have if-else blocks choose a pre-existing query.
You are correct though. Dynamic SQL can be very difficult to debug (when debugging, I add code to print the query with and without the substitution values before executing it) and can also run slower.