Is possible to create a new permanent table by a procedure?

Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.

createprocedure new_forum

@.user_idint,

@.titlevarchar(50),

@.descriptionvarchar(200)

as

begin

declare @.totalForunsint

select @.totalForuns=count(*)from foruns

set @.totalForuns= @.totalForuns+ 1

Insertinto foruns(title,creation,country,[views],[description])

values(@.title,getdate(),'England',0,@.description)

CREATETABLE [dbo].[@.totalForuns](

[thread_id] [int]IDENTITY(1,1)NOTNULL,

[user_id] [int]NOTNULL,

[last_user_id] [int]NOTNULL,

[title] [nvarchar](50)NOTNULL,

[creation] [datetime]NOTNULL,

[last_answer] [datetime]NOTNULL,

[answers] [int]NOTNULLCONSTRAINT [DF_threads_answers] DEFAULT((0)),

[vizuais] [int]NOTNULLCONSTRAINT [DF_threads_views] DEFAULT((0)),

[fixed] [bit]NOTNULLCONSTRAINT [DF_threads_fixed] DEFAULT((0)),

[votos] [int]NOTNULLCONSTRAINT [DF_threads_votos] DEFAULT(''),

CONSTRAINT [PK_threads]PRIMARYKEYCLUSTERED

(

[@.totalForuns]ASC

)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY]

)ON [PRIMARY]

GO

ALTERTABLE [dbo].[@.totalForuns] WITHCHECKADD CONSTRAINT [FK_threads_users]FOREIGNKEY([user_id])

REFERENCES [dbo].[users]([user_id])

GO

ALTERTABLE [dbo].[@.totalForuns]CHECKCONSTRAINT [FK_threads_users]

end

Go

Thank you very much.

declare @.PKtotalForunsnvarchar

set@.PKtotalForuns = "PK_"+@.totalForuns

............

CONSTRAINT [@.PKtotalForuns]PRIMARYKEYCLUSTERED

Try does it work?

|||

Thank you for reply omerkamal

The name worked fine, but is not possible to create a table with foreign keys if this table doesn't exist yet. So first I have to create this table and after configure the FKs. To create a table I just cut the last lines, but I still got error in the last line, which just says there is a error near [PRIMARY]. Here is the code:

createprocedure new_forum

@.user_idint,

@.titlevarchar(50),

@.descriptionvarchar(200)

as

begin

declare @.totalForunsint

declare @.PKtotalForunsnvarchar

select @.totalForuns=count(*)from foruns

set @.totalForuns= @.totalForuns+ 1

set @.PKtotalForuns= "PK_"+@.totalForuns

Insertinto foruns(title,creation,country,[views],[description])

values(@.title,getdate(),'England',0,@.description)

CREATETABLE [dbo].[@.PKtotalForuns](

[thread_id] [int]IDENTITY(1,1)NOTNULL,

[user_id] [int]NOTNULL,

[last_user_id] [int]NOTNULL,

[title] [nvarchar](50)NOTNULL,

[creation] [datetime]NOTNULL,

[last_answer] [datetime]NOTNULL,

[answers] [int]NOTNULLCONSTRAINT [DF_threads_answers] DEFAULT((0)),

[vizuais] [int]NOTNULLCONSTRAINT [DF_threads_views] DEFAULT((0)),

[fixed] [bit]NOTNULLCONSTRAINT [DF_threads_fixed] DEFAULT((0)),

[votos] [int]NOTNULLCONSTRAINT [DF_threads_votos] DEFAULT(''),

CONSTRAINT [PK_threads]PRIMARYKEYCLUSTERED

( [@.PKtotalForuns]ASC)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY]

)ON [PRIMARY]

Thank you again.

|||

CREATE TABLE [dbo].[@.totalForuns]( [thread_id] [int]IDENTITY(1,1)NOT NULL, [user_id] [int]NOT NULL, [last_user_id] [int]NOT NULL, [title] [nvarchar](50)NOT NULL, [creation] [datetime]NOT NULL, [last_answer] [datetime]NOT NULL, [answers] [int]NOT NULLCONSTRAINT [DF_threads_answers]DEFAULT ((0)), [vizuais] [int]NOT NULLCONSTRAINT [DF_threads_views]DEFAULT ((0)), [fixed] [bit]NOT NULLCONSTRAINT [DF_threads_fixed]DEFAULT ((0)), [votos] [int]NOT NULLCONSTRAINT [DF_threads_votos]DEFAULT (''),CONSTRAINT [@.PKtotalForuns]PRIMARY KEY CLUSTERED ( [thread_id]ASC )WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON [PRIMARY])ON [PRIMARY]

|||

Actually you will need some dynamic SQL as follows:

EXEC('CREATE TABLE [dbo].[' + @.tblname +']( [thread_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [last_user_id] [int] NOT NULL, [title] [nvarchar](50) NOT NULL, [creation] [datetime] NOT NULL, [last_answer] [datetime] NOT NULL, [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers] DEFAULT ((0)), [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views] DEFAULT ((0)), [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed] DEFAULT ((0)), [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos] DEFAULT (''''), CONSTRAINT [' + @.PKtotalForuns +'] PRIMARY KEY CLUSTERED ( [thread_id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]')

|||

I think I'll put all threads created in the same table distinguishing the forum by another FK. May be that's not the best way, because of the DB performance, but it's the quickest that I can do.

Thank you both very much.