VS Query analiser and complex sqls

Question

To retrieve data I do use SQL statement with multiple SELECT and creation of #temp tables ect.
Simply Copy-Past all that sql code to Query Builder in VS2008 does not work. Analiser can not recognise more then one SELECT statement.
How to implement complex SQL statements in C# / VB application?
Stored procedures is the one way but it is not an option.
Is it any othe way?

Need some advise where to look for or general directions.

Thank you

Igor Tiz · Answer

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.

Igor Tiz · Answer

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.

Sam Hobbs · Answer

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.