How Can you create Tables and Views Dynamically with variables??

[qmacg]

I'm new to T-Sql, but intermediate in MS Access. Below I have a section of code I've been trying to get to work in the Query Analyzer. In Access you can make up 2 variables assign values to them, and use those variables as the name of your table and field, append to the TableDefs collection. First I tried something simple, before the "create procedure" method, and that was just to declare a @variable, assign a value to it @variable = "MyTable" then write "CREATE TABLE"... then @variable. I thought the result would have been a table created named "MyTable". Instead I received an error similar to the one i'm getting on the T-Sql code below. Is this even possible to execute???

--I receive the following error
--on the code below... "Line 4: Incorrect syntax near '@tblName'."

CREATE PROCEDURE MkeTable
@tblName varchar(50)
AS
create table @tblName
(
Field varchar(50)
)

 

[tlbroadbent]

You cannot use variables for object or column names in T-SQL. You can create and execute a dynamic SQL statement.

CREATE PROCEDURE MkeTable
@tblName varchar(50)
AS
Declare @sql nvarchar(200)
Set @sql='Create Table ' + @tblName + '(Field1 varchar(50))'
Exec(@sql)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[qmacg]

Thanks very much Terry, this has helped a great deal! But, I'm trying to get another procedure to work that will call the MkeTable procedure I've saved already. In the code below, let's just say I have a list(apples, oranges etc.) in a table that I want to loop through and create the tables dynamically. I get the following error when I run this piece of code(Line 1: Incorrect syntax near 'apples'). Can you create tables dynamically by calling another procedure like this in T-Sql? I've tried the technique you've listed above (set @StrDynamExec = 'execute MkeTable ' + @StrPiece) and then execute(@StrDynamExec). Thanks again.

Here is my outer procedure...

declare @txtField1 varchar(50)
declare @txtSQLStatement varchar(50)
declare @StrDynamExec varchar(50)
declare @StrPiece varchar(50)

declare curSrchField1 cursor for
select Field1
from Dest1

open curSrchField1
fetch next from curSrchField1 into @txtField1
set @StrPiece = char(39) + @txtField1 + char(39)
EXEC Mketable @StrPiece

while (@@fetch_status <> -1)
begin

select @txtSQLStatement = @txtField1
--**I'll hold off on this line until I get the line to work before the 'begin'==> Mketable@StrPiece

--**ignore this line print @txtSQLStatement
fetch next from curSrchField1 into @txtField1
end
close curSrchField1
deallocate curSrchField1

 

[tlbroadbent]

You'll need to include quotes around the table name when calling the 2nd proc.

Set @StrDynamExec =
'Exec MkeTable ''' + @StrPiece + ''''

Execute(@StrDynamExec) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[qmacg]

Thanks again Terry... But I'm still receiving the same error (Line 1: Incorrect syntax near 'apples'.) should I remove the Char(39)'s from the @StrPiece ?? Or should I try using double quotes around the table name?

 

[tlbroadbent]

Using double quotes depends on your configuration. Do you have quoted_identifiers set on or not? I don't know why the single quotes don't work. What is displayed if you print the contents of @StrDynamExec> Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[qmacg]

Thanks for the followup Terry. Took a while for me to figure this one out. But, Looks like it wasn't anything with the quoted_identifiers. What happened was I have data inside of my source table a number (i.e. 14590). And was still receiving the error. I then tried this... cast(@strPiece as varchar(50)) to try to force the naming of a table to look like text data, because the items inside of my table could be all numbers in one record and regular text (i.e abc Company) in another(even though I declare the @strPiece as a varchar, I was trying anything to make it work). I think I read somewhere that you can't name a table with a number (i.e. 14590). Is this true? When I changed the data in my first record to (abc14590) it created the table with no problem. I can live with it, but would you know why it would not accept a number as a table name even though i've tried the cast function?
I appreciate your assistance very much, the concept of executing the the dynamic strings has helped me to grasp the T-sql logic better. :b

 

[tlbroadbent]

You cannot use a number for the first character of an object name. SQL's Rules for Identifiers states the following.

The first character must be one of the following:

A letter as defined by the Unicode Standard 2.0. The Unicode definition of letters includes Latin characters a-z and A-Z, in addition to letter characters from other languages.

The _ (underscore), @ (at sign), or # (number sign) symbol.
Certain symbols at the beginning of an identifier have special meaning in SQL Server. An identifier beginning with @ denotes a local variable or parameter. An identifier beginning with # denotes a temporary table or procedure. An identifier beginning with double number signs (##) denotes a global temporary object.

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, it is recommended that you do not use names that start with @@.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[qmacg]

Thanks again Terry, this info has been very helpful!