怎么在存储过程中创建动态的表

[pony_utc]

ALTER PROCEDURE [insert_peccancy_1]
(@SerialNum_1 [nvarchar](20))

AS
Declare @TableName nvarchar(20)

Set @TableName='peccancy'+SUBSTRING(@SerialNum_1,1,8);

CREATE TABLE @TableName(
[SerialNum] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CrossingSn] [int] NULL ,
[CarBoard] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[BoardColorSn] [int] NULL ,
[CarStyleSn] [int] NULL ,
[ChargeStyleSn] [int] NULL ,
[ChargeTypeSn] [int] NULL ,
[CarColorSn] [int] NULL ,
[PassTime] [datetime] NULL ,
[ViolationSn] [int] NULL ,
[AlarmStyleSn] [int] NULL ,
[InputState] [tinyint] NULL ,
[DataState] [int] NULL ,
[TimeSinceRed] [int] NULL ,
[FileName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Offset] [bigint] NULL ,
[NearFileLen] [bigint] NULL ,
[FarOneFileLen] [bigint] NULL ,
[FarTwoFileLen] [bigint] NULL ,
[FarThreeFileLen] [bigint] NULL ,
[RunDirection] [int] NULL ,
[CarSpeed] [float] NULL ,
[CarLength] [int] NULL ,
[FirstPosition] [int] NULL ,
[SecondPosition] [int] NULL ,
[PassUseTime] [int] NULL ,
[OperatorSn] [int] NULL ,
[AmendTime] [datetime] NULL ,
[SendTime] [datetime] NULL ,
[Reserve] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Remark] [char] (32) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

在CREATE TABLE @TableName中出错，请问怎么修改可以让我动态的生成。

[friendliu]

用EXEC('create table +'@tablename+')

[pony_utc]

现在的问题是运行上面的存储过程，他说在第十行CREATE TABLE @TableName中出错啊，
我就不知道为什么会出错啊

[friendliu]

动态的创建表不能那样写的。
你也可以这样
declare @sql varchar(8000)
set @sql='create table'
set @sql=@sql+'@tablename' +'
(
[SerialNum] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CrossingSn] [int] NULL ,
[CarBoard] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[BoardColorSn] [int] NULL ,
[CarStyleSn] [int] NULL ,
[ChargeStyleSn] [int] NULL ,
[ChargeTypeSn] [int] NULL ,
[CarColorSn] [int] NULL ,
[PassTime] [datetime] NULL ,
[ViolationSn] [int] NULL ,
[AlarmStyleSn] [int] NULL ,
[InputState] [tinyint] NULL ,
[DataState] [int] NULL ,
[TimeSinceRed] [int] NULL ,
[FileName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Offset] [bigint] NULL ,
[NearFileLen] [bigint] NULL ,
[FarOneFileLen] [bigint] NULL ,
[FarTwoFileLen] [bigint] NULL ,
[FarThreeFileLen] [bigint] NULL ,
[RunDirection] [int] NULL ,
[CarSpeed] [float] NULL ,
[CarLength] [int] NULL ,
[FirstPosition] [int] NULL ,
[SecondPosition] [int] NULL ,
[PassUseTime] [int] NULL ,
[OperatorSn] [int] NULL ,
[AmendTime] [datetime] NULL ,
[SendTime] [datetime] NULL ,
[Reserve] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Remark] [char] (32) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
'
exec(@sql)

[LoveSQL]

declare @sql varchar(8000)
set @sql='create table '+'@tablename' +'
(
[SerialNum] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CrossingSn] [int] NULL ,
[CarBoard] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[BoardColorSn] [int] NULL ,
[CarStyleSn] [int] NULL ,
[ChargeStyleSn] [int] NULL ,
[ChargeTypeSn] [int] NULL ,
[CarColorSn] [int] NULL ,
[PassTime] [datetime] NULL ,
[ViolationSn] [int] NULL ,
[AlarmStyleSn] [int] NULL ,
[InputState] [tinyint] NULL ,
[DataState] [int] NULL ,
[TimeSinceRed] [int] NULL ,
[FileName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Offset] [bigint] NULL ,
[NearFileLen] [bigint] NULL ,
[FarOneFileLen] [bigint] NULL ,
[FarTwoFileLen] [bigint] NULL ,
[FarThreeFileLen] [bigint] NULL ,
[RunDirection] [int] NULL ,
[CarSpeed] [float] NULL ,
[CarLength] [int] NULL ,
[FirstPosition] [int] NULL ,
[SecondPosition] [int] NULL ,
[PassUseTime] [int] NULL ,
[OperatorSn] [int] NULL ,
[AmendTime] [datetime] NULL ,
[SendTime] [datetime] NULL ,
[Reserve] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Remark] [char] (32) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
'
exec(@sql)

[progress99]

1.存儲過程用臨時表實現，create table #tablename...
2.用樓上的方法，EXEC('create table +'@tablename+')，先生成建表字符串，再通過
EXEC（'...')執行。

[PPLUNCLE]

做个标记...顺便帮楼主UP