For performance and security the static SQL is likely the best choice. For development time the dynamic SQL would take less time. After that, the maintenance time required is almost none for both unless changes are required. However, some changes could take the same amount of time to implement as a simple find and replace could do it in both cases. For changes that can't be put in place by find and replace then there is a chance that the dynamic SQL could be easier to maintain.

There are other factors to consider in the maintenance. The more complex your dynamic SQL the longer it takes to test and debug. This is because you have to run through every possible choice to ensure that the SQL is built properly. With static SQL you simply build it and SQL Server can check it for you and point you to bugs easily.

Also the more complex the dynamic SQL is the harder it can become to read.

I don't think you should never use dynamic SQL, I do believe you should limit its use as much as possible. There are some cases when static SQL simply won't work.

Robert Marda