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

pony_utc 2004-03-17 10:52:24
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中出错,请问怎么修改可以让我动态的生成。
...全文
37 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
PPLUNCLE 2004-03-17
  • 打赏
  • 举报
回复
做个标记...顺便帮楼主UP
progress99 2004-03-17
  • 打赏
  • 举报
回复
1.存儲過程用臨時表實現,create table #tablename...
2.用樓上的方法,EXEC('create table +'@tablename+'),先生成建表字符串,再通過
EXEC('...')執行。
LoveSQL 2004-03-17
  • 打赏
  • 举报
回复
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)
friendliu 2004-03-17
  • 打赏
  • 举报
回复
动态的创建表不能那样写的。
你也可以这样
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)
pony_utc 2004-03-17
  • 打赏
  • 举报
回复
现在的问题是运行上面的存储过程,他说在第十行CREATE TABLE @TableName中出错啊,
我就不知道为什么会出错啊
friendliu 2004-03-17
  • 打赏
  • 举报
回复
用EXEC('create table +'@tablename+')

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧