VS Query analiser and complex sqls

After playing more with complex statements some ideas pop-up.

As long as creating temp tables on SQL server side useless (it is not visible from next SELECT) I do use Datasets instead and attempting to query them..By this approach a lot of coding required and future updates is miserable.
Anyway. This is the one of potential solutions.

Have you been able to get the select statements to work if they are entered as single statements? You probably do not need to do that for all of them; if you do one or a few, you will probably determine what changes are necessary to get the others to also work.

Yes, I been able to run statement with single SELECT without any problems.

Been playing for a quite some time with it. No problems with query where single SELECT statement.

The tricky part comes when I try to put my real life statement to the query builder. The statement is really big and complicated. Involves CASE /WHEN analyzing parts, LOOPS, Creations/deletion of temp tables etc. I been able run this big statement form VS2008 Query analyzer but it gives me error. After that error (just clicking OK) query runs and retrieved data into data preview window. It not working if called from my app. Simply copy-past of existing query to query builder (VS2008) is not the best option.

As I did mentioned before, stored procedures is good alternative (and it is works just fine) but I can not use it because of our SQL server security settings.

I am looking for any other way to store big SQLs and run them.

Open for any ideas.