create a temp table inside a store procedure???

[Gary MacKay]

I'll never be able to explain why I'm doing this, but suffice it to say, this is what the client wants. It has to do with calculating scores in games.

I have a stored procedure that sql's out all the records for a player, sorts them chronologically and then walks through them one by one looking for a bunch of stuff. (Certain things can not happen before others and if they do, that record is thrown out). I need a total of 30 valid records to calculate the score, and I may have to look through 40 or 50 before I get 30 valid records. Anyway, I have this working great. Now the client wants some more stuff which is going to require me to store those 30 records in a temp table somehow and then sort them for another calculation. Can this be done? HOW??

- Gary

[Breck Carter [TeamSybase]]

Temp tables in stored procedures is a time-honored technique for
dealing with all kinds of problems, from end-user requirements to
performance improvements.

See DECLARE LOCAL TEMPORARY TABLE in the Help.

Also, CREATE LOCAL TEMPORARY TABLE also exists; it is in 9.0.2.3124, I
misremember what build it was introduced:

=====
CREATE LOCAL TEMPORARY TABLE can now be used to define a local
temporary table which will persist until the end of a connection, or
until the table is explicitly dropped. While DECLARE LOCAL TEMPORARY
TABLE can be used to do the same thing outside a procedure, there was
previously no mechanism for doing this within a procedure. In other
words, if CREATE LOCAL TEMPORARY TABLE is executed within a procedure,
the table will continue to exist after the procedure completes.
Similarly, if this statement is executed within an IF statement, the
table will exist after the IF completes.
=====

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bca...@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

[Greg Fenton]

Yes, you can create a temp table with DECLARE LOCAL TEMPORARY TABLE
outside of your stored procedure (actually outside of a BEGIN...END
block) and the table will exist for the life of your database connection
or until explicitly dropped.

Alternatively, you can CREATE GLOBAL TEMPORARY TABLE to permenantly
create the table. Your stored proc can insert, update and delete rows
from that table.

For both local and global temp tables, only the current connection can
see its own rows. The rows are not actually saved on disk and when the
connection closes all the rows disappear. The big difference between
the two is that a global table is created once and exists for all
connections even after a restart of the database, whereas a local temp
only exists for the connection that creates it and does not exist after
a restart.

In the SQLAnywhere 9.x online docs, see:

ASA SQL Reference
SQL Statements
- DECLARE LOCAL TEMPORARY TABLE statement
and
- CREATE TABLE statement

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

[Breck Carter [TeamSybase]]

I'm betting that between the two of us, Greg and I have completely
confused you with all the options :)

Let's simplify:

If you need a temporary table that is created when the procedure is
entered, and is dropped when the procedure finishes, code DECLARE
LOCAL TEMPORARY TABLE inside the procedure, up at at the front. If you
do COMMITs inside the procedure, and you don't want the temp table to
be flushed, code the ON COMMIT PRESERVE ROWS clause. Or better yet,
code NOT TRANSACTIONAL.

If you have more complex needs (broader scope, 19" rims, chrome
spinners, etc), just ask :)

Breck Monster Garage

On 2 Sep 2005 12:26:47 -0700, Gary MacKay <ga...@edisoninfo.com> wrote:

--

[Greg Fenton]

Breck Carter [TeamSybase] wrote:
> I'm betting that between the two of us, Greg and I have completely
> confused you with all the options :)
>

Breck,

You and I are good at this. ;-)

[Gary MacKay]

Thanks Greg and Breck! I followed just fine, and it works great too! Just what I was looking for.

Thanks again,
- Gary