declare @str varchar(8000) set @str = '' select @str = @str + ',' + name From sysobjects WHERE xtype='P' and status>=0 select @str = 'drop proc ' + substring(@str,2,8000) select @str exec (@str)
当然你也可以用循环或者游标来实现,但是这样用动态sql更加方便。你只要构造出语句 drop proc p1,p2,p3.. 这样的语句就可以了。
在SQL中: declare @name varchar(100) declare cur1 cursor for select name From sysobjects WHERE xtype='P' and status>=0 open cur1 fetch next cur1 into @name while @@fetch_Status=0 begin DROP PROCEDURE @name fetch next cur1 into @name end在VB程序中: dim lrecTmp as new adodb.recordset dim cn as new adodb.connection cn.connectionstring="" '===请自己添加使用的连接 lrecTmp.open "select name From sysobjects WHERE xtype='P' and status>=0",cn do while lrecTmp.eof=false cn.execute "DROP PROCEDURE " & lrecTmp.fields(0) lrecTmp.movenext loop lrecTmp.close
怎么循球使用 DROP PROCEDURE呢?能否写一段呢?
set @str = ''
select @str = @str + ',' + name From sysobjects WHERE xtype='P' and status>=0
select @str = 'drop proc ' + substring(@str,2,8000)
select @str
exec (@str)
drop proc p1,p2,p3..
这样的语句就可以了。
declare @name varchar(100)
declare cur1 cursor for
select name From sysobjects WHERE xtype='P' and status>=0
open cur1
fetch next cur1 into @name while @@fetch_Status=0
begin
DROP PROCEDURE @name
fetch next cur1 into @name
end在VB程序中:
dim lrecTmp as new adodb.recordset
dim cn as new adodb.connection
cn.connectionstring="" '===请自己添加使用的连接
lrecTmp.open "select name From sysobjects WHERE xtype='P' and status>=0",cn
do while lrecTmp.eof=false
cn.execute "DROP PROCEDURE " & lrecTmp.fields(0)
lrecTmp.movenext
loop
lrecTmp.close
在查询分析器中运行你的SQL程序有如下错误提示:服务器: 消息 102,级别 15,状态 1,行 5
在 'next' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: '@name' 附近有语法错误。
服务器: 消息 102,级别 15,状态 1,行 8
在 'next' 附近有语法错误。感谢你的解答希望回复!