Hello,

We would like to return a result set in a single query, based on a #temp table, which is created AND dropped in the same SQL statement. How can this be accomplished, because when I use the following query, I don;t get an result set (in the Advantage Architect - v10.10.0.6).

select top 10 * into #temp from mytable;

select * from #temp;

drop table #temp;

I need this to be an single SQL statement.... - with MSSQL a result set is returned...

asked 10 Sep '12, 05:44

Ronald%20Hoek's gravatar image

Ronald Hoek
61228
accept rate: 0%

edited 10 Sep '12, 05:44


The temp table must exist when the result set is returned. Instead of dropping the table before you return, I suggest you drop it the next time the script is executed:

TRY DROP TABLE #temp; CATCH ALL END TRY;

SELECT TOP 10 * INTO #temp FROM mytable;

SELECT * FROM #temp;

The TRY .. CATCH will handle the situation where the temp table doesn't yet exist. Note that temp tables are connection specific and automatically cleaned up when you disconnect.

link

answered 10 Sep '12, 06:53

Peter%20Funk's gravatar image

Peter Funk
4.3k2380
accept rate: 32%

1

OK, thanks Peter. This works for me.

(10 Sep '12, 13:02) Ronald Hoek
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×271
×101
×79

Asked: 10 Sep '12, 05:44

Seen: 1,833 times

Last updated: 10 Sep '12, 13:03

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.