SQL and dynamic table names.

Morpheus

Is it possible to dynamically name a table in SQL???

something like

CREATE TABLE

so that if my SUSER_SNAME() was "Dublin\mmorpheus",

then the table would be named "#DUBLIN\mmorpheus"

???

Karoma

yes.
bah. too damned tired to explain properly but basically, the table name can be a String - and therefore concat'd/returned (from a method)/etc ... to form the name... the SQL command is also a String , so ...

Java example:

String blah = "create table " +possiblyRerturnedOrWhatever+ "...rest if SQL"

String possiblyRerturnedOrWhatever = "arsebiscuits"

cgarvey

If you're talking of iSQL/MS SQL server stored procedures.. you can declare a string and build up a sql string, and execute that string ... from distant memory so this probably won't work out of the box...



CREATE PROCEDURE dbo.spTest
@TableName [varchar] (20),

DECLARE @sqlstr varchar(255)
SET @sqlstr = N'SELECT * FROM ' + @TableName

exec @sqlstr

...
or something like that

.cg

bonkey

Originally posted by Morphéus
Is it possible to dynamically name a table in SQL???

something like

CREATE TABLE

Assuming you're talking MS-SQL, then no.

what you would have to do is akin to what cgarvey just suggested as a workaround.

The reason is that MS-SQL parses a query at compile time, before the variables are provided. Thus, it cannot tell what you want to use as a table-name (or whether or not it is valid) and thus has no way of actually validating the CREATE statement.

If you build a VARCHAR string and then EXEC it, thats different, cause the parse of the original query will only parse that you are building a string (which is valid), and then at run-time, you will submit a proper statement to the EXEC, which can also be correctly parsed.

jc