动态创建表中出现的问题

Leshami
博客专家认证
2009-11-16 04:37:42

根据月份动态来生成表名，其格式为ResultDetail20091101-20091130,用于保存一个月的数据。
且需要动态执行增加约束和索引，然后定义为存储过程定期在每个月的1号执行




DECLARE @Month_Start DATETIME,@Month_End DATETIME;

DECLARE @Str_Start VARCHAR(10),@Str_End VARCHAR(10);

DECLARE @Tb_name VARCHAR(50),@Sql VARCHAR(2000);

DECLARE @TB_name_prefix varchar(15);



-- 设定月的开始日期、结束日期及表名

SET @Month_Start = CONVERT(char(6),getdate(),112) + '01';

SET @Month_End = CONVERT(CHAR(8),DATEADD(DAY,-1,CONVERT(CHAR(8),DATEADD(Month,1,GETDATE()),120)+'1'),112);

SET @Str_Start = CONVERT(CHAR(10),@Month_Start,112);

SET @Str_End = CONVERT(CHAR(10),@Month_End,112);

SET @Tb_name = convert(varchar(8),@Str_Start,112)+'-'+convert(varchar(8),@Str_End,112);

SET @TB_name_prefix = 'ResultDetail';

SET @Tb_name = @TB_name_prefix + @Tb_name  ;

--PRINT @Tb_name



IF  NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')

  --DROP TABLE dbo.['+@tb_name+'];



SET @Sql = '

CREATE TABLE dbo.['+@tb_name+']

(	

    [ResultID] [int] NULL,

	[IP] [varchar](15) COLLATE Latin1_General_BIN NOT NULL,

	[Action] [varchar](20) COLLATE Latin1_General_BIN NULL,

	[Host] [varchar](80) COLLATE Latin1_General_BIN NOT NULL,

	[URL] [varchar](2100) COLLATE Latin1_General_BIN NOT NULL,

	[Response] [int] NOT NULL,

	[LoadTime] [int] NULL,

	[StartTime] [int] NULL,

	[BytesOut] [bigint] NULL,

	[BytesIn] [bigint] NULL,

	[CookieSize] [int] NULL,

	[Expiration] [datetime] NULL,

	[CacheControl] [varchar](40) COLLATE Latin1_General_BIN NULL,

	[ContentType] [varchar](40) COLLATE Latin1_General_BIN NULL,

	[ContentEncoding] [varchar](10) COLLATE Latin1_General_BIN NULL,

	[ExecutionDate] [datetime] NULL,

	[FirstPacketTime] [int] NULL,

	[StartRenderTime] [int] NULL,

	[DNSLookup] [int] NULL,

	[Connections] [int] NULL,

	[SSLtime] [int] NULL,

	[TaskID] [int] NULL

) ON [PRIMARY]'

EXEC(@Sql)

--PRINT @Sql



DECLARE @Ls_Sql VARCHAR(1000)

DECLARE @Al_Count INT



--添加FOREIGN KEY

SELECT @Al_Count = Count(*) 

FROM sysobjects 

WHERE Name ='FK_ResultDetail_ResultID' + @Str_Start 

IF @Al_Count = 0

  BEGIN

	SET @Ls_Sql = 'ALTER TABLE dbo.['+@tb_name+'] 

					 ADD CONSTRAINT [FK_ResultDetail_ResultID]+['+@tb_name+']

					   FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])'

    EXEC(@Ls_Sql)

  END



--添加CHECK约束

SELECT @Al_Count = Count(*) 

FROM sysobjects 

WHERE Name ='CK_ResultDetail_ExecutionDate' + @Str_Start 

IF @Al_Count = 0

  BEGIN

	SET @Ls_Sql = 'ALTER TABLE dbo.['+@tb_name+'] 

					 ADD CONSTRAINT CK_ResultDetail_ExecutionDate'+@tb_name+']

					   CHECK (ExecutionDate >= @Month_Start AND ExecutionDate < @Month_End)'

    EXEC(@Ls_Sql)

  END



--添加索引

SELECT @Al_Count = Count(*) 

FROM sysobjects AS S1

  INNER JOIN sysindexes AS S2

    ON S1.ID = S2.ID

WHERE S1.name = 'ResultDetail'+ @Str_Start 

  AND S2.name = 'IX_ResultDetail'+ @Str_Start 

IF @Al_Count = 0

  BEGIN

	SET @Ls_Sql = 'CREATE CLUSTERED INDEX IX_ResultDetail['+@tb_name+'] 

					 ON dbo.['+@tb_name+'](ExecutionDate, Host, LoadTime)'

	EXEC(@Ls_Sql)

  END



SELECT @Al_Count = Count(*) 

FROM sysobjects AS S1

  INNER JOIN sysindexes AS S2

    ON S1.ID = S2.ID

WHERE S1.name = 'ResultDetail'+ @Str_Start 

  AND S2.name = 'IX_ResultDetail_ResultID'+ @Str_Start 

IF @Al_Count = 0

  BEGIN

	SET @Ls_Sql = 'CREATE CLUSTERED INDEX IX_ResultDetail_ResultID['+@tb_name+'] 

					 ON dbo.['+@tb_name+'](ExecutionDate, Host, LoadTime)'

	EXEC(@Ls_Sql)

  END


执行后提示的错误：
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ResultDetail20091101-20091130'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ResultDetail20091101-20091130'.

另：动态增加约束及索引时，该如何设定约束名和索引名，这个写法可能不对，[FK_ResultDetail_ResultID]+['+@tb_name+']，如果我要设定为 FK_ResultDetail_ResultID_ResultDetail20091101-20091130该如何设定？
小梁，小F在不在 ？
...全文
141 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
navy887 2009-11-16
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 robinson_0612 的回复:]
单个执行：
print出来的动态SQL 为
ALTER TABLE dbo.[ResultDetail20091101-20091130]
ADD CONSTRAINT FK_ResultDetail_ResultIDResultDetail20091101-20091130
  FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])
但执行的时候又提示
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
[/Quote]
ALTER TABLE dbo.[ResultDetail20091101-20091130] 

ADD CONSTRAINT [FK_ResultDetail_ResultIDResultDetail20091101-20091130]

  FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])
icelovey 2009-11-16
  • 打赏
  • 举报
 回复
加上方括号. "[FK_ResultDetail_ResultIDResultDetail20091101-20091130]"
[Quote=引用 16 楼 robinson_0612 的回复:]
单个执行：
print出来的动态SQL 为
ALTER TABLE dbo.[ResultDetail20091101-20091130]
ADD CONSTRAINT FK_ResultDetail_ResultIDResultDetail20091101-20091130
  FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])
但执行的时候又提示
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
[/Quote]
Leshami 2009-11-16
  • 打赏
  • 举报
 回复
单个执行：
print出来的动态SQL 为
ALTER TABLE dbo.[ResultDetail20091101-20091130]
ADD CONSTRAINT FK_ResultDetail_ResultIDResultDetail20091101-20091130
FOREIGN KEY([ResultID]) REFERENCES [dbo].[ResultMaster] ([ResultID])
但执行的时候又提示
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
忆轩辕 2009-11-16
  • 打赏
  • 举报
 回复
drop表也需要用动态SQL





IF EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')

BEGIN

    SET @Sql='DROP TABLE dbo.['+@tb_name+']'

    EXEC(@Sql)

END
Leshami 2009-11-16
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 icelovey 的回复:]
晕, 应该是EXISTS才执行DROP吧
SQL codeIFEXISTS(SELECT*FROM sysobjectsWHERE name=@Tb_nameand xtype='U')BEGINSET@Sql='DROP TABLE dbo.['+@tb_name+']'EXEC(@Sql)END
[/Quote]
是Exists，因为Drop语句不可用，
所就把那里改成 not exists
Leshami 2009-11-16
  • 打赏
  • 举报
 回复
是一步一步测试了的，
另外在拼接约束名字和索引名字，不知到如何拼接才是正确的
icelovey 2009-11-16
  • 打赏
  • 举报
 回复
晕, 应该是EXISTS才执行DROP吧


IF EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')

BEGIN

	SET @Sql='DROP TABLE dbo.['+@tb_name+']'

	EXEC(@Sql)

END
navy887 2009-11-16
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 robinson_0612 的回复:]
另：
IF  NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
  --DROP TABLE dbo.['+@tb_name+'];

DROP 语句这里报错，该如何处理，谢谢大家了
[/Quote]
IF  NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')

	DECLARE @SQL_T varchar(150);

	SET @SQL_T='DROP TABLE dbo.['+@tb_name+']'

	EXEC (@SQL_T)
icelovey 2009-11-16
  • 打赏
  • 举报
 回复
DROP报错是不是要改成这样啊???



IF  NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')

BEGIN

	SET @Sql='DROP TABLE dbo.['+@tb_name+']'

	EXEC(@Sql)

END
让你望见影子的墙 2009-11-16
  • 打赏
  • 举报
 回复
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
--DROP TABLE dbo.['+@tb_name+'];

也改为动态sql来执行

另外，下面的语句，一部分一部分的注释，来查找错误。
--小F-- 2009-11-16
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 robinson_0612 的回复:]
编译没有错误
执行的时候报错，
我要将外键设定为 FK_ResultDetail_ResultID
加上 _ResultDetail20091101-20091130的组合，该如何设定？

[/Quote]

这个可以直接修改啊
用alter语句
--小F-- 2009-11-16
  • 打赏
  • 举报
 回复
编译没错误 就print出来看看
Leshami 2009-11-16
  • 打赏
  • 举报
 回复
编译没有错误
执行的时候报错，
我要将外键设定为 FK_ResultDetail_ResultID
加上 _ResultDetail20091101-20091130的组合，该如何设定？
sgtzzc 2009-11-16
  • 打赏
  • 举报
 回复
动态SQL，最直接的检查方法就是print出来
--小F-- 2009-11-16
  • 打赏
  • 举报
 回复
编译报什么错误？
SQL77 2009-11-16
  • 打赏
  • 举报
 回复
帮顶
--小F-- 2009-11-16
  • 打赏
  • 举报
 回复
强大 等高手来
Leshami 2009-11-16
  • 打赏
  • 举报
 回复
另：
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = @Tb_name and xtype='U')
--DROP TABLE dbo.['+@tb_name+'];

DROP 语句这里报错，该如何处理，谢谢大家了
Mybatis 动态创建表
Mybatis 动态创建表 注意,创建表时使用的时${} 而不是#{} ${} 和 #{} ${} 的变量替换是在动态SQL解析阶段, 仅仅为一个纯碎的 string 替换,会存在sql注入问题 #{} 在变量赋值前会被解析为一个占位符?,之后在DBMS中将括号内的值插入,因此传入当传入 "’‘1’’"会出现语法错误 复制一张表作为新表 mapper接口 // 复制原有的表 void copyTable(@Param("newTable")String newTable,@Param
C语言之动态创建链表的两种方式
之前我们说的链表是基于静态创建的，但静态创建的链表很不灵活而且在工作中的项目中的很多场景都不适合，所以我们来记录一下链表的两种动态创建的方式。 1.方式一：链表动态创建之头插法 那么我们直接上代码讲： struct Test* insertLinkfrontHead(struct Test *head,struct Test *new) {//动态创建链表之头插法 if(head == NULL) { head = new; } else { new->next = h
Mysql之存储过程，动态创建表和数据库-,动态删除-yellowcong
今天在弄mycat 的时候，有一个需求，就是创建12个数据库，然后数据库里面都创建一个表，我当时想象，这个玩意也不难，于是寻思通过存储过程来动态创建表，只需要传递进来数据的前缀名称和表名称，即可创建12个表，每个库里面都有一个固定格式的表。存储过程中，不能有tab制表符，不然就报错 存储过程存储过程的动态建表和sql，需要依赖于PREPARE 和EXECUTE 函数,通过动态生成表的方式，大大的加速
（Demo）mysql存储过程创建动态创建数据库，数据表
mysql存储过程创建动态创建数据库，数据表 1.过程：自动创建数据库，从一个表中查出需要创建数据的名称，然后创建数据库。需要依赖于PREPARE 和EXECUTE 函数, 刚开始安装正常思路直接查，然后赋值，最后创建。但是出现了一个问题 这个时候创建的数据库不是动态的，会一直创建库名为baseName的数据库。 经过查资料，创建数据库，表是不能这么写的，需要写成动态的sql语句才可以。 ...
kafka动态创建消费者
使用注解的方式不能动态创建消费者，而且需要改变原代码，并重启项目，这往往只会增加后期维护成本。如题目所示，动态创建消费者，在现实的应用场景中显得越来越重要。
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章