Using variable parameter to create table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using variable parameter to create table

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2009-03-12 : 14:57:06

I ahve the following script to create a table with a variable parameter

declare @name varchar(15)
set @name = 'test'

CREATE TABLE [dbo].[@name](
	[ID] [int] NOT NULL,
	[Event] [int] NOT NULL,
	[Gross] [float] NULL,
	[Net] [float] NULL,
 CONSTRAINT [@name] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[Event] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The code gets an error when I try to run it. In the create table command and the constraint how do I need to code this to work with the @name?

Thx

LH

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-12 : 15:03:06

you need to use dynamic sql.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-12 : 15:04:58

declare @name varchar(15), @SQL varchar(500)
set @name = 'test'

Set @SQL = '
CREATE TABLE [dbo].[' + @name + '](
[ID] [int] NOT NULL,
[Event] [int] NOT NULL,
[Gross] [float] NULL,
[Net] [float] NULL,
CONSTRAINT [' + @name + '] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Event] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@SQL)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lionheart
Starting Member

41 Posts

Posted - 2009-03-12 : 15:12:18

Thansk so much. If I wanted to change the name so it includes the year and date hwo could I do this?

LH

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 00:55:14

SET @Name = CAST(YEAR(GetDate()) AS VARCHAR(4)) + '_' + CONVERT(VARCHAR(8),GetDate(),112)

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:26:20

can i ask purpose of creating new tables for each day of year?

Lionheart
Starting Member

41 Posts

Posted - 2009-03-13 : 13:35:22

All

Thansk for your help. Have starting working around with another solution, but have definitely learned some great stuff from this.

LH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:46:15

ok...you should be trying to hold all info in single table with a date column to denote date when info was entered

Lionheart
Starting Member

41 Posts

Posted - 2009-03-13 : 14:15:59

That is what I ended up with. The original request came from a colleague, and when I stepped away from trying to sort the mechanics I thought of the alternative, which is as you mention, and have created that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 14:19:26

cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources