数据库中有许多如下形式的表:
a 表有字段bh,mc ,bz
b 表中有字段bh,mc ,bz
c 表中有字段bh1,mc ,bz
d 表有字段mc ,bz
e,f,g...
清空记录的规则
1,表名为e,f,g 的记录不能清空
2,数据表中无 “bh” 字段的表的记录不能清空,比如表c,d 等
3,写一存储过程,传入参数@bh,在满足1,2 条件的情况下,清空数据库中
表,字段bh等于传入参数的表的记录
a 表有字段bh,mc ,bz
b 表中有字段bh,mc ,bz
c 表中有字段bh1,mc ,bz
d 表有字段mc ,bz
e,f,g...
清空记录的规则
1,表名为e,f,g 的记录不能清空
2,数据表中无 “bh” 字段的表的记录不能清空,比如表c,d 等
3,写一存储过程,传入参数@bh,在满足1,2 条件的情况下,清空数据库中
表,字段bh等于传入参数的表的记录
解决方案 »
- 求一个查询语句
- 客户端无法连接远程sqlserver2005 Analysis Service?
- reporting service问题。。。
- sql相同值之间的间隔次数和连续出现的次数
- 多个if else 语句的使用
- 我是复制的表,可是如何用sql语句查看主键呢?比如下面表Customers的主键
- SQL中难题(累计与清零问题)敬请各位SQL高手进来指教!
- 检索出一条记录,如果知道该纪录在表中的位置(第几行)?
- 助人为乐!!!!!!!!!!!
- 交流一下:有关pb6.0与sql server中的问题我是llsyhy(小胖)100分,来者有分。!
- 在线求:如何批量修改字段名?
- 这样在差分器中 显示行标
@col nvarchar(100)
asdeclare @table nvarchar(100);declare curTables cursor for select name from sys.objects where type='U' and name not in('e','f','g')
and object_ID not in (select a.object_ID from sys.columns a inner join sys.objects b
on a.object_id = b.object_id where b.type='U' and a.name ='ID');
open curTables;
fetch curTables into @table;
while @@fetch_status =0
begin
exec(N'truncate table ' + @table );
fetch curTables into @table;
end
close curTables;
deallocate curTables;
Create Proc SP_ClearTables
@col nvarchar(100)
asdeclare @table nvarchar(100);declare curTables cursor for select name from sys.objects where type='U' and name not in('e','f','g')
and object_ID not in (select a.object_ID from sys.columns a inner join sys.objects b
on a.object_id = b.object_id where b.type='U' and a.name =@col);
open curTables;
fetch curTables into @table;
while @@fetch_status =0
begin
exec(N'truncate table ' + @table );
fetch curTables into @table;
end
close curTables;
deallocate curTables;
set @colName ='bh'
declare CKCr cursor
for select [name] from sysobjects where xtype='U' and [name]<>'dtproperties'
open CKCr
declare @tblName sysname
fetch CKCr into @tblname
begin
if (@colname<>'e') and (@colname<>'f') and (@colname<>'g')
begin
if exists(select * from sys.columns where [object_id]=OBJECT_ID(N'+@tblname+') and name=@colName)
exec ('delete from '+@tblname)
end
end
while @@fetch_status=0
begin
fetch next from CKCr into @tblname
begin
if (@colname<>'e') and (@colname<>'f') and (@colname<>'g')
begin
if exists(select * from sys.columns where [object_id]=OBJECT_ID(N'+@tblname+') and name=@colName)
exec ('delete from '+@tblname)
end
end
end
close CKCr试试
sysobjects where xtype='U' 这表示什么?
@bh nvarchar(100)
as
declare CKCr cursor
for select [name] from sysobjects where xtype='U' and [name]<>'dtproperties' and [name]<>'e' and [name]<>'f' and [name]<>'g'
open CKCr
declare @tblName sysname
fetch CKCr into @tblname
if exists(select * from sys.columns where [object_id]=OBJECT_ID(N'+@tblname+') and name=@bh)
exec ('delete from '+@tblName +' where bh='+@bh)
while @@fetch_status=0
begin
fetch next from CKCr into @tblname
begin
if exists(select * from sys.columns where [object_id]=OBJECT_ID(N'+@tblname+') and name=@bh)
exec ('delete from '+@tblName+' where bh='+@bh)
end
end
close CKCr
@col nvarchar(100)
as
declare @table nvarchar(100);declare curTables cursor for select name from sys.objects where type='U' and name not in('e','f','g')
and object_ID not in (select a.object_ID from sys.columns a inner join sys.objects b
on a.object_id = b.object_id where b.type='U' and a.name ='bh');
open curTables;
fetch curTables into @table;
while @@fetch_status =0
begin
exec(N'delete from ' + @table+' where bh= '+@col);
fetch curTables into @table;
end
close curTables;
deallocate curTables;
对象名 'sys.objects' 无效。
对象名 'sys.columns' 无效。
对象名 'sys.objects' 无效。
drop procedure pr_LZ
go
create procedure pr_LZ @bh varchar(50)
as
begin
declare @nID int
declare @cName varchar(100)
declare @nCnt int
declare @cSQL varchar(2000)
-- 1,表名为e,f,g 的记录不能清空
declare myCur cursor for
select [ID], [name]
from sysobjects
where type = 'U' and
[name] not in( 'e', 'f', 'g' )
open myCur
fetch next from myCur into @nID, @cName
while @@fetch_status=0
begin
select @nCnt = count(1)
from syscolumns
where [id] = @nID and
name = @bh
-- 2,数据表中无 “bh” 字段的表的记录不能清空,比如表c,d 等
if( @nCnt > 0 )
begin
set @cSQL = 'delete from ' + @cName
exec( @cSQL )
end
fetch next from myCur into @nID, @cName
end
close myCur
deallocate myCur
end
go