Create a table in a stored procedure dynamically

[choic]

Hi there

Is there any way to create a table in a stored procedure dynamically where the table name is based on a variable?
I did something as follows but it does not work.

Declare @sql varchar(255)
Declare @table_name varchar(30)
Select @table_name = 'my_table_01_2001'
Select @sql = 'create table ' + @table_name
+ ' (column_1 int primary key)'
Exec(@sql)

Please help.

Ricky

 

[Tim1]

Hiya Ricky,

Which version of Sybase are you using? Some will not support dynamic SQL.

Tim

 

[choic]

Hi Tim,

Mine is version 11.92. Does it support dynamic SQL?

Thanks for your help.

Ricky

 

[Tim1]

Hi Ricky,

Yes, it should do (any version above 11.5 should support dynamic SQL). What error message do you get, because I cannot see anything immediately wrong with your code.

Tim

 

[choic]

Hi Tim

I tried a few different ways but all are prompted with errors:

Example 1:

DECLARE @tablename varchar(30)
SELECT @tablename = 'hi'
CREATE TABLE @tablename (column_1 int)

Error: Incorrect syntax near '@tablename'.

Example 2:

DECLARE @tablename varchar(30)
DECLARE @sql varchar(200)
SELECT @tablename = 'hi'
SELECT @sql = 'CREATE TABLE ' + @tablename + ' (column_1 int)'
EXEC @sql

Error: The name 'CREATE TABLE hi (column_1 int)' is not a valid identifier.

Do you have any ideas of how to fix this problem? Thanks.

Ricky

 

[Tim1]

Hi Ricky,

The first example will, of course, fail because a table name beginning '@' is not valid, hence the error message.

On the second one, the problem is that you are using DDL (sorry I didn't mention this before). You cannot imbed DDL into a dynamic statement:

Try :

DECLARE @sql varchar(200)
DECLARE @tablename varchar(30)
SELECT @tablename = 'hi'
SELECT @sql = @tablename + ' (column_1 int)'

EXEC ("CREATE TABLE " @sql)

I think that should work, otherwise you may need

EXEC ("CREATE TABLE "@tablename "column_1 int&quot

I have never used dynamic SQL to build a table, just working from the DROP TABLE logic.

HTH

Tim