CODEWITHSUNDEEP
sybasetemp-tablessap-ase
user1318369
user1318369

Reputation: 715

Creating temp tables in sybase

I am running into an issue with creating temp tables in Sybase db. We have a sql where we create a temp table, insert/update it and do a select * from it at the end of get some results. We are invoking this sql from the service layer using spring jdbc tmplate. The first run works fine, but the next subsequesnt runs fails with error

cannot create temporary table <name>. Prefix name is already in use by another temorary table

This is how I am checking if table exists:

if object_id('#temp_table') is not null
drop table #temp_table

create table #temp_table(
...
)

Anything I am missing here?

Upvotes: 0

Views: 15656

Answers (3)

markp-fuso
markp-fuso

Reputation: 34184

NOTE: A bit more info on BigDaddyO's first suggestion ...

The code snippet you've provided, when submitted as a SQL batch, is parsed as a single unit of work prior to the execution. Net result is that if #temp_table already exists when the batch is submitted, then the compilation of the create table command will generate the error. This behavior can be seen in the following example:

create table #mytab (a int, b varchar(30), c datetime)
go

-- your code snippet; during compilation the 'create table' generates the error
-- because ... at the time of compilation #mytab already exists:

if object_id('#mytab') is not NULL
     drop table #mytab
create table #mytab (a int, b varchar(30), c datetime)
go

Msg 12822, Level 16, State 1:
Server 'ASE200', Line 3:
Cannot create temporary table '#mytab'. Prefix name '#mytab' is already in use by another temporary table '#mytab'.

-- same issue occurs if we pull the 'create table' into its own batch:

create table #mytab (a int, b varchar(30), c datetime)
go

Msg 12822, Level 16, State 1:
Server 'ASE200', Line 1:
Cannot create temporary table '#mytab'. Prefix name '#mytab' is already in use by another temporary table '#mytab'.

As BigDaddyO has suggested, one way to get around this is to break your code snippet into two separate batches, eg:

-- test/drop the table in one batch:

if object_id('#mytab') is not NULL
     drop table #mytab
go

-- create the table in a new batch; during compilation we don't get an error
-- because #mytab does not exist at this point:

create table #mytab (a int, b varchar(30), c datetime)
go

Upvotes: 0

RobbZ
RobbZ

Reputation: 1410

You are really close but temp tables require using the db name before too. 

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
  DROP TABLE #Results
GO

It would be the same if you were checking if a user table in another database existed.

IF OBJECT_ID('myDatabase..myTable') IS NOT NULL
  DROP TABLE myDatabase..myTable
GO

Upvotes: 1

BigDaddyO
BigDaddyO

Reputation: 36

Might not be a great response, but I also have that problem and I have 2 ways around it. 1. Do the IF OBJECT_ID Drop Table as a separate execute prior to the query 2. Do the Drop Table without the IF OBJECT_ID() right after your query.

Upvotes: 2

Related Questions