有这样一个问题:从一个维护表tabn中查出符合条件的表,然后删除那些表中的记录,怎么做?
如:有表 A、B、C、D、E....
在表tabn中:tabname tabdate
A 2009-10
B 2009-10
C 2009-11
D 2009-10
E 2009-10
要存储过程实现想查出表名,然后一个一个删除它怎么实现,可以吗?
CREATE PROCEDURE deltab
AS
declare @sql varchar(1000),@tabname varchar(1000),@i int ,@icount int select @icount=count(*) from tabn where tabdate='2009-10'set @i = 0
while @i < @icount
begin
select @tabname=tabname from tabn where tabdate='2009-10'
set @sql='delete from '+@tabname
exec (@sql)
set @i = @i + 1
end好象做不到如ADO.recordset的RS.movenext,又没办法把结果存到一个变量集中,怎么办?是不是只能用游标?不用游标行不行?
希望高手帮忙解答一下,我是新手,分不多,但我一定会给的,只要能解答清楚。
如:有表 A、B、C、D、E....
在表tabn中:tabname tabdate
A 2009-10
B 2009-10
C 2009-11
D 2009-10
E 2009-10
要存储过程实现想查出表名,然后一个一个删除它怎么实现,可以吗?
CREATE PROCEDURE deltab
AS
declare @sql varchar(1000),@tabname varchar(1000),@i int ,@icount int select @icount=count(*) from tabn where tabdate='2009-10'set @i = 0
while @i < @icount
begin
select @tabname=tabname from tabn where tabdate='2009-10'
set @sql='delete from '+@tabname
exec (@sql)
set @i = @i + 1
end好象做不到如ADO.recordset的RS.movenext,又没办法把结果存到一个变量集中,怎么办?是不是只能用游标?不用游标行不行?
希望高手帮忙解答一下,我是新手,分不多,但我一定会给的,只要能解答清楚。
delete from tabn wherewhere tabdate='2009-10' ,一句sql就搞定了,不用循环
delete from B
.....
这些表的全部记录
Create proc deleteTb@Date DateTimeasDeclare @TableName varchar(2000)
SET @TableName=(Select ','+name from tb where tbDate=Convert(varchar(10),@Date,120) for xml path(''))
SET @TableName=Replace(@TableName,',',' delete from ')
Exec(@TableName)
-------------TEST----------------
--deleteTb '2009-10 '
Create proc deleteTb@Date DateTimeasDeclare @TableName varchar(2000)
SET @TableName=(Select ','+name from tb where tbDate=Convert(varchar(10),@Date,120) for xml path(''))
SET @TableName=Replace(@TableName,',',' delete from ')
Exec(@TableName)--deleteTb '2009-10 '
SET @TableName=(Select ','+name from tb where tbDate='200910' for xml path(''))
SET @TableName=Replace(@TableName,',',' delete from ')
exec(@TableName)服务器: 消息 170,级别 15,状态 1,过程 deleteTb,行 8
第 8 行: 'xml' 附近有语法错误。
kasin000 兄好象在SQL2005上可行,因为我没有SQL2005,所以没有真正测试,
我只有SQL2000+sp4:所以只支持FOR XML [RAW|AUTO|EXPLIT] 没有PATH?怎么办?在SQL2000上,
Alter proc deleteTb @Date varchar(10) as Declare @TableName varchar(500)
SET @TableName=''
Select @TableName=@TableName+','+tbname from tb where tbDate=@Date
SET @TableName=Replace(@TableName,',',' delete from ') Exec(@TableName)
--deleteTb '2009-11'
kasin000兄,太谢谢了!
对了,就是这样。
还有个小问题请问一下:就是如果 tb就是如果是参数的话,怎么改?
Alter proc deleteTb @Date varchar(10) ,
@tb varchar(100) as Declare @TableName varchar(500)
SET @TableName=''
Select @TableName=@TableName+','+tbname from @tb where tbDate=@Date
SET @TableName=Replace(@TableName,',',' delete from ') Exec(@TableName)服务器: 消息 137,级别 15,状态 2,过程 deleteTb,行 11
必须声明变量 '@tb'。
@Date varchar(10)
as exec(
'
Declare @TableName varchar(500)SET @TableName=''''
Select @TableName=@TableName+'',''+tbname from '+@TbName+' where tbDate='''+@Date+'''
SET @TableName=Replace(@TableName,'','','' delete from '') Exec(@TableName)'
)
--deleteTb 'tb','2009-11'