SQL Server

Creating Table by using Stored Procedure dynamically

Create PROCEDURE sp_TP_CreateTable

(

@TABLENAME as varchar(max)

)

AS

declare @c varchar(max)='[dbo].['+@TABLENAME+']'

declare @b varchar(max)='drop table'+@c

declare @d varchar(max)='

CREATE TABLE'+@c+' (

[Description] [varchar] (255) ,

[Catalog Version] [varchar] (255) ,

[Mailed] [float] NULL ,

[Sales] [money] NULL ,

[Production $] [int] NOT NULL ,

[Orders] [float] NULL ,

[Response] [float] NULL ,

[Response of Test Control] [int] NOT NULL ,

[Average Invoice] [float] NULL ,

[SMP] [float] NULL ,

[SMP of Test Control] [int] NOT NULL ,

[Catalog Title] [varchar] (255)  ,

[Brand] [varchar] (1),

[Drop Date] [smalldatetime] NULL

) ON [PRIMARY]

'

print @c

print @b

print @d

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@TABLENAME+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

exec(@b)

exec(@d)

Go

Executing above Stored Procedure:

exec sp_TP_CreateTable2 'pra'