Create SQL table with dynamic name
I need a store procedure in SQL that will create a table with a dynamic name. The passed value to the stored procedure is of the form nnnnn-nnnnn (can be any digits) and I know that - is not valid in a table name.
The stored procedure I created (only part of it, the part that is causing me issues) has these commands
DECLARE @tblname nvarchar(30),
@str1 nvarchar(100),
@str2 nvarchar(max),
@str3 nvarchar(max) ;
SET @tblname = REPLACE(@txnname, '-', '_');
SET @str1 = N'CREATE TABLE ' + @tblname + ' ';
SET @str2 = N'sonum varchar(15), itemno varchar(30), itemdesc varchar(150), itemqty decimal(15,5), itemcost decimal(15,5), itemamount decimal(15,5)';
SET @str3 = @str1 + @str2;
EXEC sp_executesql @str3
The REPLACE operator transforms the '-' to '_' which is what I want.
What I don't want is for the resulting table to be named dbo.+@tblname+ which is what happens when I execute the stored procedure, passing it
12345-54321
I tired passing it '12345-54321' by including ' but also produced an expression error.
Feel certain I have done something stupid. Can you help rescue me?
SET @str1 = N'CREATE TABLE [' + convert(varchar, @tblname) + '] ';
and use 12345-54321
SET @str1 = N'CREATE TABLE [' + @tblname + N'] ';
SET @str1 = N'CREATE TABLE ' + QUOTENAME(@schemaname) + '.' + QUOTENAME(@tblname) + ' ';
SET @str1 = N'CREATE TABLE ' +
CASE WHEN CHARINDEX('.',@tblname) > 0
THEN QUOTENAME(LEFT(@tblname, CHARINDEX('.',@tblname)-1)) + '.' +
QUOTENAME(SUBSTRING(@tblname, CHARINDEX('.',@tblname)+1,LEN(@tblname)))
ELSE QUOTENAME(@tblname)
END
SET @str2 = N'(sonum varchar(15), itemno varchar(30), itemdesc varchar(150), itemqty decimal(15,5), itemcost decimal(15,5), itemamount decimal(15,5))';
p.s. you're looking for QUOTENAME()..