sybase question: how to create a table with dynamic tablename

[Thomas Vogt]

Hi

I need to create a table within a stored procedure, whose dynamic name
consists of a string and a parameter, i.e. something like

create procedure Foo (@param1 varchar(30), @param2 varchar(30)) as ...
...
select @tablename = "ThisString" + @param1 create table @tablename (bar
varchar)

as the identifier of a tablename cannot be a varchar, it somehow should be
converted. my sybase version is 11.9.2.3

regards
Thomas Vogt

[Tim Vernum]

Thomas Vogt <tho...@bsdunix.ch> wrote in message news:<pan.2002.11.19.14....@bsdunix.ch>...

> I need to create a table within a stored procedure, whose dynamic name
> consists of a string and a parameter

This is not directly supported.
Tablenames cannot be variables.

You will need to use Dynamic SQL.

> my sybase version is 11.9.2.3

Dynamic SQL is not directly supported in 11.9, you will need to "fake it"
See: http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.10

[rvennik]

Upgrade to v12++ and you can use "execute immediate" like this:

create proc mk_tab (@tab varchar(32) )
as
begin
declare @cmd varchar(16000)
select @cmd = 'create table '+@tab+ ' ( col1 int, col2 int NULL )'
exec (@cmd)
end

PS varchar(16000) is NOT possible in v12.0

Ramon

Originally posted by Thomas Vogt

--
Posted via http://dbforums.com

[Bernd Dulfer]

> I need to create a table within a stored procedure, whose dynamic name
> consists of a string and a parameter, i.e. something like

Since ASE 11.9.2 doesn't support dynamic sql you have two options:

Use the sp_remotesql procedure to execute sql on the server. See the
documentation in the online docs or the description at Rob Verschoors
web site (www.sypron.nl).

Or create the table with a default name, that would never be used and
use sp_rename to give it the real name.
You have to do this in two separate procedures.
I think Rob has an example for this as well.