create table A(id int,a varchar(6),b varchar(6),c varchar(6),d varchar(6),e varchar(6)) insert into A select 1,'面包','牛奶','尿布','啤酒',NULL insert into A select 2,'可乐','牛奶','尿布',NULL ,NULL insert into A select 3,'面包','可乐','牛奶',NULL ,NULL insert into A select 4,'可乐','啤酒',NULL ,NULL ,NULL insert into A select 5,'可乐','牛奶','尿布','花生',NULL insert into A select 6,'可乐','牛奶','花生',NULL ,NULL insert into A select 7,'面包','可乐','牛奶','尿布','啤酒' insert into A select 8,'面包','牛奶','尿布','花生','啤酒' insert into A select 9,'面包',NULL ,NULL ,NULL ,NULL gocreate procedure shiyan(@tname varchar(10),@num int) as begin declare @sql varchar(8000) set @sql=''
select @sql=@sql+'+(case when '+name+' is null then 0 else 1 end)' from syscolumns where id=object_id(@tname) and name!='id'
set @sql='delete '+@tname+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num)
exec(@sql) end goexec shiyan 'A',3 select * from A /* id a b c d e ----------- ------ ------ ------ ------ ------ 1 面包 牛奶 尿布 啤酒 NULL 4 可乐 啤酒 NULL NULL NULL 5 可乐 牛奶 尿布 花生 NULL 7 面包 可乐 牛奶 尿布 啤酒 8 面包 牛奶 尿布 花生 啤酒 9 面包 NULL NULL NULL NULL */ goexec shiyan 'A',1 select * from A /* id a b c d e ----------- ------ ------ ------ ------ ------ 1 面包 牛奶 尿布 啤酒 NULL 4 可乐 啤酒 NULL NULL NULL 5 可乐 牛奶 尿布 花生 NULL 7 面包 可乐 牛奶 尿布 啤酒 8 面包 牛奶 尿布 花生 啤酒 */ godrop table A drop procedure shiyan go
create table A(id int, a int ,b int,c int,d int,e int) insert into A select 1, 9,8,7,null,null union select 2,8,8,8,7,null union select 3,9,8,6,null,nullGo create proc test @table varchar(50), @num int AS declare @sql varchar(8000) set @num=3 set @table ='A' set @sql='' select @sql=@sql+'+case when '+name+' is not null then 1 else 0 end ' from syscolumns where object_id(@table)=id and name<>'id' order by colid select @sql='delete '+@table+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num) exec( @sql) GO exec dbo.test 'A',3select * from A /* id a b c d e ----------- ----------- ----------- ----------- ----------- ----------- 2 8 8 8 7 NULL */ drop table a drop proc test
--這個也可以 Create ProceDure shiyan(@TableName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = '' Select @S = @S + ' Union All Select id, ' + Name + ' From ' + @TableName + ' Where ' + Name + ' Is Not Null' From SysColumns Where ID = OBJECT_ID(@TableName) And Name != 'id' Order By ColID Select @S = ' Delete From A Where id In ( Select ID From( ' + Stuff(@S, 1, 11, '') + ' ) T Group By id Having Count(id) = ' + Rtrim(@Count) + ')' Print @S EXEC(@S) End GO
Create Table A (id Nvarchar(10), a Nvarchar(10), b Nvarchar(10), c Nvarchar(10), d Nvarchar(10), e Nvarchar(10)) GO Insert A Select 1, N'面包', N'牛奶', N'尿布', N'啤酒', NULL Union All Select 2, N'可乐', N'牛奶', N'尿布', NULL, NULL Union All Select 3, N'面包', N'可乐', N'牛奶', NULL, NULL Union All Select 4, N'可乐', N'啤酒', NULL, NULL, NULL Union All Select 5, N'可乐', N'牛奶', N'尿布', N'花生', NULL Union All Select 6, N'可乐', N'牛奶', N'花生', NULL, NULL Union All Select 7, N'面包', N'可乐', N'牛奶', N'尿布', N'啤酒' Union All Select 8, N'面包', N'牛奶', N'尿布', N'花生', N'啤酒' Union All Select 9, N'面包', NULL, NULL, NULL, NULL GOCreate ProceDure shiyan(@TableName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = '' Select @S = @S + ' Union All Select id, ' + Name + ' From ' + @TableName + ' Where ' + Name + ' Is Not Null' From SysColumns Where ID = OBJECT_ID(@TableName) And Name != 'id' Order By ColID Select @S = ' Delete From A Where id In ( Select ID From( ' + Stuff(@S, 1, 11, '') + ' ) T Group By id Having Count(id) = ' + Rtrim(@Count) + ')' Print @S EXEC(@S) End GO exec shiyan 'A', 3 Select * From Aexec shiyan 'A', 1 Select * From A GO Drop Table A Drop ProceDure shiyan --Result /* id a b c d e 1 面包 牛奶 尿布 啤酒 NULL 4 可乐 啤酒 NULL NULL NULL 5 可乐 牛奶 尿布 花生 NULL 7 面包 可乐 牛奶 尿布 啤酒 8 面包 牛奶 尿布 花生 啤酒 9 面包 NULL NULL NULL NULLid a b c d e 1 面包 牛奶 尿布 啤酒 NULL 4 可乐 啤酒 NULL NULL NULL 5 可乐 牛奶 尿布 花生 NULL 7 面包 可乐 牛奶 尿布 啤酒 8 面包 牛奶 尿布 花生 啤酒 */
libin_ftsafe(子陌红尘) ( ) 信誉:105 Blog 加为好友 2007-04-13 17:15:08 得分: 0
看到在每一行第一列为null时后面都是为null.所以: create procedure shiyan(@tablename varchar(10),@num int) as begin declare @sql varchar(200) set @sql='select id from into deltable'+@tablename+'where '+(case num when 1 then 'b is null and a is not null' when 2 then 'c is null and b is not null' when 3 then 'd is null and c is not null' when 4 then 'e is null and d is not null' when 5 then 'e is not null'else '' end) exec (@sql) delete @tablename where id in (select id from deltable) end go
Jackie_GP這個ID偶有印象,好像跟偶短消息過....
zhengzeng() ,表名是動態,列名也是動態的,你那麼寫有問題的。
Create Table A (id Nvarchar(10), a Nvarchar(10), b Nvarchar(10), c Nvarchar(10), d Nvarchar(10), e Nvarchar(10)) GO Insert A Select 1,N'面包',N'牛奶',N'尿布',N'啤酒',NULL Union All Select 2,N'可乐',N'牛奶',N'尿布',NULL,NULL Union All Select 3,N'面包',N'可乐',N'牛奶',NULL,NULL Union All Select 4,N'可乐',N'啤酒',NULL,NULL,NULL Union All Select 5,N'可乐',N'牛奶',N'尿布',N'花生',NULL Union All Select 6,N'可乐',N'牛奶',N'花生',NULL,NULL Union All Select 7,N'面包',N'可乐',N'牛奶',N'尿布',N'啤酒' Union All Select 8,N'面包',N'牛奶',N'尿布',N'花生',N'啤酒' Union All Select 9,N'面包',NULL,NULL,NULL,NULL GOcreate procedure shiyan(@tablename varchar(10),@num int) as begin declare @sql varchar(200) set @sql='select id into deltable from '+@tablename+' where '+(case @num when 1 then 'b is null and a is not null' when 2 then 'c is null and b is not null' when 3 then 'd is null and c is not null' when 4 then 'e is null and d is not null' when 5 then 'e is not null'else '' end) select @sql exec (@sql) delete A where id in (select id from deltable) drop table deltable end goexec shiyan 'A',2 select * from A Drop Table A Drop ProceDure shiyan /* 1 面包 牛奶 尿布 啤酒 NULL 2 可乐 牛奶 尿布 NULL NULL 3 面包 可乐 牛奶 NULL NULL 5 可乐 牛奶 尿布 花生 NULL 6 可乐 牛奶 花生 NULL NULL 7 面包 可乐 牛奶 尿布 啤酒 8 面包 牛奶 尿布 花生 啤酒 9 面包 NULL NULL NULL NULL */
create procedure sp_test(@tname varchar(10),@sub int) as begin exec('delete '+@tname+' where sub='+rtrim(@sub)) end go
--如果固定有sub列 Create Table B (sub Int, a Nvarchar(10), b Nvarchar(10)) GO Insert B Select 3, N'花生', N'牛奶' Union All Select 3, N'可乐', N'尿布' Union All Select 5, N'可乐', N'牛奶' Union All Select 3, N'面包', N'尿布' Union All Select 4, N'面包', N'牛奶' Union All Select 3, N'面包', N'啤酒' Union All Select 5, N'尿布', N'牛奶' Union All Select 3, N'尿布', N'啤酒' Union All Select 3, N'牛奶', N'啤酒' GO Create ProceDure shiyan(@TableName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From B Where sub = ' + Rtrim(@Count) EXEC(@S) End GO exec shiyan 'B', 3 Select * From B GO Drop Table B Drop ProceDure shiyan --Result /* sub a b 5 可乐 牛奶 4 面包 牛奶 5 尿布 牛奶 */
create table B(sub int,a varchar(10),b varchar(10)) insert into B select 3,'花生','牛奶' insert into B select 3,'可乐','尿布' insert into B select 5,'可乐','牛奶' insert into B select 3,'面包','尿布' insert into B select 4,'面包','牛奶' insert into B select 3,'面包','啤酒' insert into B select 5,'尿布','牛奶' insert into B select 3,'尿布','啤酒' insert into B select 3,'牛奶','啤酒' gocreate procedure shiyan(@tname varchar(10),@sub int) as begin declare @sql varchar(2000) set @sql='delete '+@tname+' where sub='+rtrim(@sub) exec(@sql) end goexec shiyan 'B',3 select * from B /* sub a b ----------- ---------- ---------- 5 可乐 牛奶 4 面包 牛奶 5 尿布 牛奶 */ godrop table B drop procedure shiyan go
--列名也是作為一個參數? 那這麼寫--如果固定有sub列 Create Table B (sub Int, a Nvarchar(10), b Nvarchar(10)) GO Insert B Select 3, N'花生', N'牛奶' Union All Select 3, N'可乐', N'尿布' Union All Select 5, N'可乐', N'牛奶' Union All Select 3, N'面包', N'尿布' Union All Select 4, N'面包', N'牛奶' Union All Select 3, N'面包', N'啤酒' Union All Select 5, N'尿布', N'牛奶' Union All Select 3, N'尿布', N'啤酒' Union All Select 3, N'牛奶', N'啤酒' GO Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From B Where ' + @ColName + '= ' + Rtrim(@Count) EXEC(@S) End GO exec shiyan 'B', 'sub', 3 Select * From B GO Drop Table B Drop ProceDure shiyan --Result /* sub a b 5 可乐 牛奶 4 面包 牛奶 5 尿布 牛奶 */
Jackie_GP(欲让其灭亡,先让其疯狂) ( ) 信誉:100 Blog 加为好友 2007-04-13 17:56:35 得分: 0
--上面存儲過程有點bugCreate Table B (sub Int, a Nvarchar(10), b Nvarchar(10)) GO Insert B Select 3, N'花生', N'牛奶' Union All Select 3, N'可乐', N'尿布' Union All Select 5, N'可乐', N'牛奶' Union All Select 3, N'面包', N'尿布' Union All Select 4, N'面包', N'牛奶' Union All Select 3, N'面包', N'啤酒' Union All Select 5, N'尿布', N'牛奶' Union All Select 3, N'尿布', N'啤酒' Union All Select 3, N'牛奶', N'啤酒' GO Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From ' + @TableName +' Where ' + @ColName + '= ' + Rtrim(@Count) EXEC(@S) End GO exec shiyan 'B', 'sub', 3 Select * From B GO Drop Table B Drop ProceDure shiyan --Result /* sub a b 5 可乐 牛奶 4 面包 牛奶 5 尿布 牛奶 */
--如果只有一個int列Create Table B (sub Int, a Nvarchar(10), b Nvarchar(10)) GO Insert B Select 3, N'花生', N'牛奶' Union All Select 3, N'可乐', N'尿布' Union All Select 5, N'可乐', N'牛奶' Union All Select 3, N'面包', N'尿布' Union All Select 4, N'面包', N'牛奶' Union All Select 3, N'面包', N'啤酒' Union All Select 5, N'尿布', N'牛奶' Union All Select 3, N'尿布', N'啤酒' Union All Select 3, N'牛奶', N'啤酒' GO Create ProceDure shiyan(@TableName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From ' +@TableName +' Where ' + Name + '= ' + Rtrim(@Count) From SysColumns Where ID = OBJECT_ID(@TableName) And Xtype = 56 EXEC(@S) End GO exec shiyan 'B', 3 Select * From B GO Drop Table B Drop ProceDure shiyan --Result /* sub a b 5 可乐 牛奶 4 面包 牛奶 5 尿布 牛奶 */
--三個參數的寫法 --創建存儲過程 Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From ' + @TableName +' Where ' + @ColName + '= ' + Rtrim(@Count) EXEC(@S) End GO --調用 exec shiyan 'B', 'sub', 3 Select * From B GO --如果只有一個int列,只有兩個參數的寫法 --創建存儲過程 Create ProceDure shiyan(@TableName Varchar(100), @Count Int) As Begin Declare @S Varchar(8000) Select @S = ' Delete From ' +@TableName +' Where ' + Name + '= ' + Rtrim(@Count) From SysColumns Where ID = OBJECT_ID(@TableName) And Xtype = 56 EXEC(@S) End GO --調用 exec shiyan 'B', 3 Select * From B
——————————————————
3表示就是一行中不是NULL的列有3列。
as
begin
declare @sql varchar(8000)
set @sql=''
select
@sql=@sql+'+(case when '+name+' is null then 0 else 1 end)'
from
syscolumns
where
id=object_id(@name) and name!='id'
set @sql='delete '+@tname+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num)
exec(@sql)
end
go
先做一个函数,返回当前行中不等于NULL的列的个数。
然后再做个存储过程,把这个函数的值等于输入个数的行显示出来就好了。
insert into A select 1,'面包','牛奶','尿布','啤酒',NULL
insert into A select 2,'可乐','牛奶','尿布',NULL ,NULL
insert into A select 3,'面包','可乐','牛奶',NULL ,NULL
insert into A select 4,'可乐','啤酒',NULL ,NULL ,NULL
insert into A select 5,'可乐','牛奶','尿布','花生',NULL
insert into A select 6,'可乐','牛奶','花生',NULL ,NULL
insert into A select 7,'面包','可乐','牛奶','尿布','啤酒'
insert into A select 8,'面包','牛奶','尿布','花生','啤酒'
insert into A select 9,'面包',NULL ,NULL ,NULL ,NULL
gocreate procedure shiyan(@tname varchar(10),@num int)
as
begin
declare @sql varchar(8000)
set @sql=''
select
@sql=@sql+'+(case when '+name+' is null then 0 else 1 end)'
from
syscolumns
where
id=object_id(@tname) and name!='id'
set @sql='delete '+@tname+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num)
exec(@sql)
end
goexec shiyan 'A',3
select * from A
/*
id a b c d e
----------- ------ ------ ------ ------ ------
1 面包 牛奶 尿布 啤酒 NULL
4 可乐 啤酒 NULL NULL NULL
5 可乐 牛奶 尿布 花生 NULL
7 面包 可乐 牛奶 尿布 啤酒
8 面包 牛奶 尿布 花生 啤酒
9 面包 NULL NULL NULL NULL
*/
goexec shiyan 'A',1
select * from A
/*
id a b c d e
----------- ------ ------ ------ ------ ------
1 面包 牛奶 尿布 啤酒 NULL
4 可乐 啤酒 NULL NULL NULL
5 可乐 牛奶 尿布 花生 NULL
7 面包 可乐 牛奶 尿布 啤酒
8 面包 牛奶 尿布 花生 啤酒
*/
godrop table A
drop procedure shiyan
go
http://community.csdn.net/Expert/topic/5430/5430127.xml?temp=.7200586
http://community.csdn.net/Expert/topic/5430/5430132.xml?temp=.1538813
http://community.csdn.net/Expert/topic/5430/5430157.xml?temp=.4114649
http://community.csdn.net/Expert/topic/5430/5430135.xml?temp=1.947284E-04
http://community.csdn.net/Expert/topic/5440/5440170.xml?temp=.5692865
insert into A
select 1, 9,8,7,null,null
union
select 2,8,8,8,7,null
union
select 3,9,8,6,null,nullGo
create proc test
@table varchar(50),
@num int
AS
declare @sql varchar(8000)
set @num=3
set @table ='A'
set @sql=''
select @sql=@sql+'+case when '+name+' is not null then 1 else 0 end ' from syscolumns where object_id(@table)=id
and name<>'id'
order by colid
select @sql='delete '+@table+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num)
exec( @sql)
GO
exec dbo.test 'A',3select * from A
/*
id a b c d e
----------- ----------- ----------- ----------- ----------- -----------
2 8 8 8 7 NULL
*/
drop table a
drop proc test
Create ProceDure shiyan(@TableName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union All Select id, ' + Name + ' From ' + @TableName + ' Where ' + Name + ' Is Not Null' From SysColumns Where ID = OBJECT_ID(@TableName) And Name != 'id' Order By ColID
Select @S = ' Delete From A Where id In ( Select ID From( ' + Stuff(@S, 1, 11, '') + ' ) T Group By id Having Count(id) = ' + Rtrim(@Count) + ')'
Print @S
EXEC(@S)
End
GO
(id Nvarchar(10),
a Nvarchar(10),
b Nvarchar(10),
c Nvarchar(10),
d Nvarchar(10),
e Nvarchar(10))
GO
Insert A Select 1, N'面包', N'牛奶', N'尿布', N'啤酒', NULL
Union All Select 2, N'可乐', N'牛奶', N'尿布', NULL, NULL
Union All Select 3, N'面包', N'可乐', N'牛奶', NULL, NULL
Union All Select 4, N'可乐', N'啤酒', NULL, NULL, NULL
Union All Select 5, N'可乐', N'牛奶', N'尿布', N'花生', NULL
Union All Select 6, N'可乐', N'牛奶', N'花生', NULL, NULL
Union All Select 7, N'面包', N'可乐', N'牛奶', N'尿布', N'啤酒'
Union All Select 8, N'面包', N'牛奶', N'尿布', N'花生', N'啤酒'
Union All Select 9, N'面包', NULL, NULL, NULL, NULL
GOCreate ProceDure shiyan(@TableName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ' Union All Select id, ' + Name + ' From ' + @TableName + ' Where ' + Name + ' Is Not Null' From SysColumns Where ID = OBJECT_ID(@TableName) And Name != 'id' Order By ColID
Select @S = ' Delete From A Where id In ( Select ID From( ' + Stuff(@S, 1, 11, '') + ' ) T Group By id Having Count(id) = ' + Rtrim(@Count) + ')'
Print @S
EXEC(@S)
End
GO
exec shiyan 'A', 3
Select * From Aexec shiyan 'A', 1
Select * From A
GO
Drop Table A
Drop ProceDure shiyan
--Result
/*
id a b c d e
1 面包 牛奶 尿布 啤酒 NULL
4 可乐 啤酒 NULL NULL NULL
5 可乐 牛奶 尿布 花生 NULL
7 面包 可乐 牛奶 尿布 啤酒
8 面包 牛奶 尿布 花生 啤酒
9 面包 NULL NULL NULL NULLid a b c d e
1 面包 牛奶 尿布 啤酒 NULL
4 可乐 啤酒 NULL NULL NULL
5 可乐 牛奶 尿布 花生 NULL
7 面包 可乐 牛奶 尿布 啤酒
8 面包 牛奶 尿布 花生 啤酒
*/
红尘之前回答過樓主的問題吧。
----------------------------------------------
回帖太多,没有印象......
-------------
我以為你回過類似的帖子,才反應這麼快呢。 :)
create procedure shiyan(@tablename varchar(10),@num int)
as
begin
declare @sql varchar(200)
set @sql='select id from into deltable'+@tablename+'where '+(case num
when 1 then 'b is null and a is not null' when 2 then 'c is null and b is not null' when 3 then 'd is null and c is not null' when 4 then
'e is null and d is not null' when 5 then
'e is not null'else '' end)
exec (@sql)
delete @tablename where id in (select id from deltable)
end
go
(id Nvarchar(10),
a Nvarchar(10),
b Nvarchar(10),
c Nvarchar(10),
d Nvarchar(10),
e Nvarchar(10))
GO
Insert A Select 1,N'面包',N'牛奶',N'尿布',N'啤酒',NULL
Union All Select 2,N'可乐',N'牛奶',N'尿布',NULL,NULL
Union All Select 3,N'面包',N'可乐',N'牛奶',NULL,NULL
Union All Select 4,N'可乐',N'啤酒',NULL,NULL,NULL
Union All Select 5,N'可乐',N'牛奶',N'尿布',N'花生',NULL
Union All Select 6,N'可乐',N'牛奶',N'花生',NULL,NULL
Union All Select 7,N'面包',N'可乐',N'牛奶',N'尿布',N'啤酒'
Union All Select 8,N'面包',N'牛奶',N'尿布',N'花生',N'啤酒'
Union All Select 9,N'面包',NULL,NULL,NULL,NULL
GOcreate procedure shiyan(@tablename varchar(10),@num int)
as
begin
declare @sql varchar(200)
set @sql='select id into deltable from '+@tablename+' where '+(case @num
when 1 then 'b is null and a is not null' when 2 then 'c is null and b is not null' when 3 then 'd is null and c is not null' when 4 then
'e is null and d is not null' when 5 then
'e is not null'else '' end)
select @sql
exec (@sql)
delete A where id in (select id from deltable)
drop table deltable
end
goexec shiyan 'A',2
select * from A Drop Table A
Drop ProceDure shiyan
/*
1 面包 牛奶 尿布 啤酒 NULL
2 可乐 牛奶 尿布 NULL NULL
3 面包 可乐 牛奶 NULL NULL
5 可乐 牛奶 尿布 花生 NULL
6 可乐 牛奶 花生 NULL NULL
7 面包 可乐 牛奶 尿布 啤酒
8 面包 牛奶 尿布 花生 啤酒
9 面包 NULL NULL NULL NULL
*/
sub a b
3 花生 牛奶
3 可乐 尿布
5 可乐 牛奶
3 面包 尿布
4 面包 牛奶
3 面包 啤酒
5 尿布 牛奶
3 尿布 啤酒
3 牛奶 啤酒
exec 存储过程的名字 表名,个数
就删除个数是几的行,例如:exec shiyan 'B',3,就删除所有为3的行,非常谢谢!
as
begin
exec('delete '+@tname+' where sub='+rtrim(@sub))
end
go
Create Table B
(sub Int,
a Nvarchar(10),
b Nvarchar(10))
GO
Insert B Select 3, N'花生', N'牛奶'
Union All Select 3, N'可乐', N'尿布'
Union All Select 5, N'可乐', N'牛奶'
Union All Select 3, N'面包', N'尿布'
Union All Select 4, N'面包', N'牛奶'
Union All Select 3, N'面包', N'啤酒'
Union All Select 5, N'尿布', N'牛奶'
Union All Select 3, N'尿布', N'啤酒'
Union All Select 3, N'牛奶', N'啤酒'
GO
Create ProceDure shiyan(@TableName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From B Where sub = ' + Rtrim(@Count)
EXEC(@S)
End
GO
exec shiyan 'B', 3
Select * From B
GO
Drop Table B
Drop ProceDure shiyan
--Result
/*
sub a b
5 可乐 牛奶
4 面包 牛奶
5 尿布 牛奶
*/
insert into B select 3,'花生','牛奶'
insert into B select 3,'可乐','尿布'
insert into B select 5,'可乐','牛奶'
insert into B select 3,'面包','尿布'
insert into B select 4,'面包','牛奶'
insert into B select 3,'面包','啤酒'
insert into B select 5,'尿布','牛奶'
insert into B select 3,'尿布','啤酒'
insert into B select 3,'牛奶','啤酒'
gocreate procedure shiyan(@tname varchar(10),@sub int)
as
begin
declare @sql varchar(2000)
set @sql='delete '+@tname+' where sub='+rtrim(@sub)
exec(@sql)
end
goexec shiyan 'B',3
select * from B
/*
sub a b
----------- ---------- ----------
5 可乐 牛奶
4 面包 牛奶
5 尿布 牛奶
*/
godrop table B
drop procedure shiyan
go
--------------------------------------------------
呵呵,我先贴的那个又有BUG.
错误如下
服务器: 消息 207,级别 16,状态 3,行 1
列名 'sub' 无效。
请二位高手再次帮忙,非常谢谢!!!
Create Table B
(sub Int,
a Nvarchar(10),
b Nvarchar(10))
GO
Insert B Select 3, N'花生', N'牛奶'
Union All Select 3, N'可乐', N'尿布'
Union All Select 5, N'可乐', N'牛奶'
Union All Select 3, N'面包', N'尿布'
Union All Select 4, N'面包', N'牛奶'
Union All Select 3, N'面包', N'啤酒'
Union All Select 5, N'尿布', N'牛奶'
Union All Select 3, N'尿布', N'啤酒'
Union All Select 3, N'牛奶', N'啤酒'
GO
Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From B Where ' + @ColName + '= ' + Rtrim(@Count)
EXEC(@S)
End
GO
exec shiyan 'B', 'sub', 3
Select * From B
GO
Drop Table B
Drop ProceDure shiyan
--Result
/*
sub a b
5 可乐 牛奶
4 面包 牛奶
5 尿布 牛奶
*/
paoluo(一天到晚游泳的鱼) 和libin_ftsafe(子陌红尘) 的第二个存储过程都有点小问题,我要求存储过程的两个参数都是动态的(表的列名是动态的)。
请二位高手再次帮忙,非常谢谢!!!
------
你刪除做判斷的列名必須可以確定下來。要麼像我上面那樣,有三個參數。或者你的表只有一個int列,也可以。
(sub Int,
a Nvarchar(10),
b Nvarchar(10))
GO
Insert B Select 3, N'花生', N'牛奶'
Union All Select 3, N'可乐', N'尿布'
Union All Select 5, N'可乐', N'牛奶'
Union All Select 3, N'面包', N'尿布'
Union All Select 4, N'面包', N'牛奶'
Union All Select 3, N'面包', N'啤酒'
Union All Select 5, N'尿布', N'牛奶'
Union All Select 3, N'尿布', N'啤酒'
Union All Select 3, N'牛奶', N'啤酒'
GO
Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From ' + @TableName +' Where ' + @ColName + '= ' + Rtrim(@Count)
EXEC(@S)
End
GO
exec shiyan 'B', 'sub', 3
Select * From B
GO
Drop Table B
Drop ProceDure shiyan
--Result
/*
sub a b
5 可乐 牛奶
4 面包 牛奶
5 尿布 牛奶
*/
(sub Int,
a Nvarchar(10),
b Nvarchar(10))
GO
Insert B Select 3, N'花生', N'牛奶'
Union All Select 3, N'可乐', N'尿布'
Union All Select 5, N'可乐', N'牛奶'
Union All Select 3, N'面包', N'尿布'
Union All Select 4, N'面包', N'牛奶'
Union All Select 3, N'面包', N'啤酒'
Union All Select 5, N'尿布', N'牛奶'
Union All Select 3, N'尿布', N'啤酒'
Union All Select 3, N'牛奶', N'啤酒'
GO
Create ProceDure shiyan(@TableName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From ' +@TableName +' Where ' + Name + '= ' + Rtrim(@Count) From SysColumns Where ID = OBJECT_ID(@TableName) And Xtype = 56
EXEC(@S)
End
GO
exec shiyan 'B', 3
Select * From B
GO
Drop Table B
Drop ProceDure shiyan
--Result
/*
sub a b
5 可乐 牛奶
4 面包 牛奶
5 尿布 牛奶
*/
--創建存儲過程
Create ProceDure shiyan(@TableName Varchar(100), @ColName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From ' + @TableName +' Where ' + @ColName + '= ' + Rtrim(@Count)
EXEC(@S)
End
GO
--調用
exec shiyan 'B', 'sub', 3
Select * From B
GO
--如果只有一個int列,只有兩個參數的寫法
--創建存儲過程
Create ProceDure shiyan(@TableName Varchar(100), @Count Int)
As
Begin
Declare @S Varchar(8000)
Select @S = ' Delete From ' +@TableName +' Where ' + Name + '= ' + Rtrim(@Count) From SysColumns Where ID = OBJECT_ID(@TableName) And Xtype = 56
EXEC(@S)
End
GO
--調用
exec shiyan 'B', 3
Select * From B