当前位置：首页 → 问答吧 → 【求助】如何对返回的结果做操作？

【求助】如何对返回的结果做操作？

时间：2011-11-05

来源：互联网

SQL code

select   *   from   sysobjects   where   id   in   (select   id   from   syscolumns   where   name= 'approvedate')

这样我会得到很多表名如何将这些表依次执行下面的删除操作？

SQL code

declare @k datetime 
declare @j datetime 
set @k='2009-04-20 10:10:11.000' 
set @j='2009-04-23 19:53:11.000'

delete FROM RegisterReport
WHERE approveDate
BETWEEN @k AND @j;

作者: guojimail 发布时间: 2011-11-05

游标解决

作者: NBDBA 发布时间: 2011-11-05

SQL code

declare @sql nvarchar(max)

declare @k datetime 
declare @j datetime 
set @k='2009-04-20 10:10:11.000' 
set @j='2009-04-23 19:53:11.000'

select @sql=isnull(@sql,'')+' delete FROM '+name +' WHERE approveDate BETWEEN '''+convert(varchar,@k,120)+''' AND '''+convert(varchar,@j,120) +''';'
from sysobjects where id in (select id from syscolumns where name= 'approvedate') 

exec(@sql)

作者: qianjin036a 发布时间: 2011-11-05

谢谢

那个，报了个错误。

消息 4405，级别 16，状态 1，第 1 行
视图或函数 'V_RisReport' 不可更新，因为修改会影响多个基表。

引用 2 楼 qianjin036a 的回复:

SQL code
declare @sql nvarchar(max)

declare @k datetime
declare @j datetime
set @k='2009-04-20 10:10:11.000'
set @j='2009-04-23 19:53:11.000'

select @sql=isnull(@sql,'')+' delete FROM '+name +'……

作者: guojimail 发布时间: 2011-11-05

在另一贴已回复过了

作者: roy_88 发布时间: 2011-11-05

SQL code

declare @k datetime 
declare @j datetime 
set @k='2009-04-20 10:10:11.000' 
set @j='2009-04-23 19:53:11.000'

--delete FROM RegisterReport
--WHERE approveDate
--BETWEEN @k AND @j;

--生成语句直接执行
select ' delete '+name+'  where  approvedate between '''+CONVERT(varchar(23),@k,120)+''' and '''+CONVERT(varchar(23),@k,120)+''' ;'
from sysobjects as a,syscolumns as b where a.id=b.id and b.name='approvedate'

--或用变量传参一次执行
declare @s nvarchar(max)
select @s=@s+' delete '+name+'  where  approvedate between '''+CONVERT(varchar(23),@k,120)+''' and '''+CONVERT(varchar(23),@k,120)+''' ;'
from sysobjects as a,syscolumns as b where a.id=b.id and b.name='approvedate'

exec(@s)

在生成语句可以看到生成的格式，下面变量，只是把生成的语句用一变量传参，再执行

作者: roy_88 发布时间: 2011-11-05

少改一个位置，a.Name=表名
SQL code

declare @k datetime 
declare @j datetime 
set @k='2009-04-20 10:10:11.000' 
set @j='2009-04-23 19:53:11.000'

--delete FROM RegisterReport
--WHERE approveDate
--BETWEEN @k AND @j;

--生成语句直接执行
select ' delete 'a.+name+'  where  approvedate between '''+CONVERT(varchar(23),@k,120)+''' and '''+CONVERT(varchar(23),@k,120)+''' ;'
from sysobjects as a,syscolumns as b where a.id=b.id and b.name='approvedate'

--或用变量传参一次执行
declare @s nvarchar(max)
select @s=@s+' delete '+a.name+'  where  approvedate between '''+CONVERT(varchar(23),@k,120)+''' and '''+CONVERT(varchar(23),@k,120)+''' ;'
from sysobjects as a,syscolumns as b where a.id=b.id and b.name='approvedate'

exec(@s)

当然调用系统存储过程的方法

SQL code

sp_msforeachtable 'if exists(select 1 from syscolumns where id=object_id(''?'') and objectproperty(object_id(''?''),''IsTable'')=1 and Name=''approvedate'')
delete a from ? as a where approvedate between ''2009-04-20 10:10:11'' and ''2009-04-23 19:53:11'''

作者: roy_88 发布时间: 2011-11-05

【求助】如何对返回的结果做操作？

热门阅读

热门下载