SP Temp Tables

[navdeep virk]

Hi All,

I am creating temp tables and dropping them after the processing is done in
a stored procedure , however if users abort and try to run again we get the
error " t_temp etc.. already in the database" . Is there any workaround to
check if this temp table exists then drop it and create the temp table again
..


Navdeep

__________________________________________________ _______________
See when your friends are online with MSN Messenger 6.0. Download it now
FREE! [url]http://msnmessenger-download.com[/url]

sending to informix-list

[Paul Watson]

If the user aborts in the SPL code you can trap on the exception and
tidy up. Otherwise always drop the table first and then use the
exception
to resume a failed drop.

navdeep virk wrote:

>
> Hi All,
>
> I am creating temp tables and dropping them after the processing is done in
> a stored procedure , however if users abort and try to run again we get the
> error " t_temp etc.. already in the database" . Is there any workaround to
> check if this temp table exists then drop it and create the temp table again
> .
>
> Navdeep
>
> __________________________________________________ _______________
> See when your friends are online with MSN Messenger 6.0. Download it now
> FREE! [url]http://msnmessenger-download.com[/url]
>
> sending to informix-list

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #

[rkusenet]

"navdeep virk" <nvirk@msn.com> wrote in message news:bnbo7c$k58$1@terabinaries.xmission.com...

>
> Hi All,
>
> I am creating temp tables and dropping them after the processing is done in
> a stored procedure , however if users abort and try to run again we get the
> error " t_temp etc.. already in the database" . Is there any workaround to
> check if this temp table exists then drop it and create the temp table again
> .

There are two ways of dealing with this problem.

1. Create an exception block to ignore errors and drop the temp table at the
start of the procedure. No error will be returned if the table does not exists.

BEGIN
ON EXCEPTION
--
END EXCEPTION WITH RESUME

DROP TABLE TMP_TABLE ;

END

2. Run this query at the start of the SP.

SELECT count(*)
into w_count
from sysmaster:systabnames s,sysmaster:systabinfo i
where i.ti_partnum = s.partnum
and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
and s.tabname = 'your_tmp_table_name' ;

The query will return 1 if the temp table exists.

I would go for option (1).

Ravi



--

email id is bogus

[Gregg Walker]

Here's a procedure I have that does the same kind of thing you're talking
about. You could essentialy add the begin end block logic to the top of
your procedure.

Hope this helps.

Gregg Walker

Create Procedure ClearShiftEntries(
pShiftCardID integer
)

--set debug file to "/tmp/ClearShiftEntries.out";
--trace on;

begin

on exception in (-206)

begin

create temp table tShiftEntry (
shift_card_id integer not null,
line_num smallint default 0 not null,
entry_time datetime year to second not null,
clock_entry_id integer
)
with no log;

end

end exception with resume

delete from
tShiftEntry
where
shift_card_id = pShiftCardID;

end

End Procedure; -- ClearShiftEntries

"navdeep virk" <nvirk@msn.com> wrote in message
news:bnbo7c$k58$1@terabinaries.xmission.com...

>
> Hi All,
>
> I am creating temp tables and dropping them after the processing is done

in

> a stored procedure , however if users abort and try to run again we get

the

> error " t_temp etc.. already in the database" . Is there any workaround to
> check if this temp table exists then drop it and create the temp table

again

> .
>
>
> Navdeep
>
> __________________________________________________ _______________
> See when your friends are online with MSN Messenger 6.0. Download it now
> FREE! [url]http://msnmessenger-download.com[/url]
>
> sending to informix-list

[Jean Sagi]

As you could see there are some "ways" to lead to this "issue"...

What I wondwer is: Why this happen? Why a delete delay to delete?

Funny...


Chucho!

navdeep virk wrote:

> Hi All,
>
> I am creating temp tables and dropping them after the processing is done
> in a stored procedure , however if users abort and try to run again we
> get the error " t_temp etc.. already in the database" . Is there any
> workaround to check if this temp table exists then drop it and create
> the temp table again .
>
>
> Navdeep
>
> __________________________________________________ _______________
> See when your friends are online with MSN Messenger 6.0. Download it now
> FREE! [url]http://msnmessenger-download.com[/url]
>
> sending to informix-list
>
>

--


Atte,


Jesús Antonio Santos Giraldo
[email]jeansagi@myrealbox.com[/email]
[email]jeansagi@netscape.net[/email]

sending to informix-list

[Unregistered]

do this before using the temp table

if object_id("[tempdb].[dbo].[#TempTableName]") is not null drop #TempTableName
create table #TempTableName
(
column1 DataType1,
column2 DataType2,
...
)


This solves the problem.
-Yordanos

[yordanos]

do this before using the temp table

if object_id("[tempdb].[dbo].[#TempTableName]") is not null drop #TempTableName
create table #TempTableName
(
column1 DataType1,
column2 DataType2,
...
)


This solves the problem.
-Yordanos