How to create a temporary table with variable name in Sybase ASE?

If someone asks you if we can create a table with variable name or with a runtime generated name, then what would be your answer?

Ofcourse as you are reading this article so the answer should be - "Yes ofcourse". The rest of the article will tell you how to do the same. First let me tell you that ofcourse this approach has a short coming of its own but its fun to learn how we will achieve this thing and the shortcoming is that in case we run same script or stored procedure from two different sessions at nearly the same time then one of the session might get error that the table already exists in the system catalogue. Okay, so lets get back to the approach and you will yourself be able to understand the short coming that I have listed here.

So, coming back to work, the situation with us is as follows -

Situation - We want to create a table which will created and dropped on each session but we do not want to use the session temp table.

With the above problem statement we are sure about one thing and that is - We need to create table uniquely.
So what to do in this case as the table will be generated at runtime and we will have no control over the name. Well, we need to think about some unique value which will be unique atleast for a specific session. So what could that be? Well, as we are everytime talking about session session and only session here so what could be better than a session id... Yes, right on track.. So we will use the session id for our job and use the Sybase ASE global variable @@spid to serve our purpose.

Now as we have got the unique id to help us create unique table names so we will write the following sp to serve our purpose -

create procedure MyProc1
as
begin

declare
   @MyTabName varchar(100)

create table MyTab
(
    Id int,
    FirstName varchar(100),
    LastName varchar(100)
)

select @MyTabName = 'MyTab' + convert(varchar, @@spid)

sp_rename MyTab, @MyTabName

print 'Renamed the table MyTab to a unique table name'

select * from sysobjects where name like @MyTabName

end
go

And thats it... As we can see in the above stored procedure we have been able to create a unique table in Sybase ASE without using session temp table or without using dynamic sql. As you can see here that the only place we find a problem with the above stored procedure is when we create the table MyTab and rename it to the new table name. The problem here is that as the two statements are not in the transaction so there might be a case that in case of parallel execution of the same stored procedure we get the error that the table name already exists in the system catalogue. But this can be avoided if we use 'ddl in trans' db option for our Sybase ASE configuration.

Well thats all for now.... will be back with more fun sometime later.. Have fun till then....