The advantage of using a stored proc is that the stored proc will only be compiled once per restart of the database. If you send over the SQL it needs to be compiled each time. The advantage of sending it over is that the code is less complicated, as you can tailor the amount of unions (I presume you know how many unions you need before sending over the SQL code). The stored procedure will be more complicated as you don't know how many unions you're going to need when writing the stored proc.

That's the trade-off you need to make, and only you have sufficient knowledge to make the right decision. If you're going to do this stuff millions of times, you're probably better off using a stored proc. If you do this once a day, I'd go for the simpler code. If it's somewhere in between, well, good luck making the decision! ;-)