sql server programming: variable for table name in CREATE TABLE statement -- sql server programming

variable for table name in CREATE TABLE statement

kumar s
11/14/2003 9:43:32 PM

sql server programming: Can I pass a variable for the table name when i use
CREATE TABLE DDL statement.

If @tblName is declared as a variable of data type varchar
and a values is assigned from a cursor, is it possible to
create a table by name assigned to this variable.

CREATE TABLE [testDB].[dbo].[@tblName]
(
fldID INT IDENTITY(1,1) PRIMARY KEY,
fldName VARCHAR(25) NOT NULL UNIQUE,
controlType CHAR(1) NOT NULL,
fldRemarks VARCHAR(250) NULL
)

The above one created a table with table name @tblName,
but I need the table name to be the value stored in this
variable.

Re: variable for table name in CREATE TABLE statement

Steve Kass
11/15/2003 12:57:13 AM

Kumar,

Two possibilities:

Create the table as [AboutToChange],
then use sp_rename to change its name, since
sp_rename can accept a variable for the new name.

or

use dynamic SQL:

declare @sql nvarchar(1000)
set @sql = '
create table [testDB].dbo.@tblName
( ...
)'
set @sql = replace(@sql,'@tblName',quotename(@tblName))
exec (@sql)

SK

[quoted text, click to view]

Re: variable for table name in CREATE TABLE statement

Joe Celko
11/15/2003 4:01:07 PM

[quoted text, click to view]
CREATE TABLE DDL statement. <<

No. The klduge is to use dynamic SQL and I am sure that someone else
will help you destroy the data integrity of your DBMS.

The right answer is to stop writing code like this!! Why would you
create new tables on the fly in an application? Don't you have a data
model?

OO programmers who have no SQL or relational training sometimes do this.
The stupiest example I can remember was one guy who was creating and
destroying tables that represented individual shopping carts on a
website.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Re: variable for table name in CREATE TABLE statement

Aaron Bertrand [MVP]
11/15/2003 10:50:07 PM

[quoted text, click to view]

Hey, I remember that too. Does that make me old school? :-)

Re: variable for table name in CREATE TABLE statement

Joe Celko
11/17/2003 10:44:38 AM

[quoted text, click to view]

Nah! Using punch cards from the back of your desk drawer for book marks
makes you old school :)

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.