Variable Table Identifiers

[Jeff J]

I am creating tables dynamically using stored procedures, and would like to use a parameter variable as the table_name identifier. The SPs are implemented in T-SQL scripts. I am currently using literal identifiers as usual, then renaming the tables via sp_rename. For example (with SET QUOTED_IDENTIFIER ON):

CREATE PROC spCreateTable (@TableName sysname)
AS
BEGIN
CREATE TABLE "_TempTableName_" (
/* ... */
)

EXEC sp_rename '_TempTableName_', @TableName --rename to desired name
END
GO

Does anyone know how I could use @TableName directly or indirectly in the CREATE TABLE statement?

[Rob Graham]

CREATE PROC spCreateTable (@TableName sysname)
AS
BEGIN
declare @Tb NVarchar(80)
select @tb = N'[dbo].[' + @tb + N']'

CREATE TABLE @Tb(
/* ... */
) ON [PRIMARY]

END
GO

works for me... Some ideas are so stupid that only an intellectual could have thought of them - George Orwell

[Jeff J]

That was what I originally did, but I keep getting invalid identifier errors with SQL Server 2K. I was hoping this was just something specific to T-SQL, as opposed to the syntaxes I am more familiar with. There must be a switch I'm incorrectly setting somewhere, since your script files parse correctly. Thanks for confirming my original approach is not what I need to change. Cheers

[Rob Graham]

Hmmm.. This works fine with SQL 2k for me, However I usually don't change the Quoted Identifiers from the default (which I believe is off...). I always rebuild the table name into an NVARCHR local variable and delimit with the []. I think whwn we went from SQL7 to 2k the table owner part also became necessary [dbo]. The invalid identifiesr error suggests either white space or illegal characters in the name... Some ideas are so stupid that only an intellectual could have thought of them - George Orwell