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.