我是想将以下ASP中的代码,转成SQL存储过程,前辈们帮帮忙,学习:<%
strSql="select id,nsort1,filename from upload where id>1 and id<=1000 order by id"
set rs=conn.Execute(strSql)
if rs_1.eof and rs_1.bof thenelse
do while not rs_1.eof
select case rs_1("nsort1")
case "article"
strSql="select 1 from article where charindex('"&rs_1("filename")&"',word)>0"
case "forum"
strSql="select 1 from bbs_data where charindex('"&rs_1("filename")&"',word)>0"
Case Else
strSql=""
end select
If strSql<>"" then
set rs=server.createobject("adodb.recordset")
rs.open strSql,conn,1,1
If rs.RecordCount>0 Then
strSql=""
Else
conn.execute("update upload set iid=0,types=0 where id="&rs_1("id"))
End If
rs.close:set rs=Nothing
End if
rs_1.movenext
loop
rs_1.close:set rs_1=Nothing
end if
%>
strSql="select id,nsort1,filename from upload where id>1 and id<=1000 order by id"
set rs=conn.Execute(strSql)
if rs_1.eof and rs_1.bof thenelse
do while not rs_1.eof
select case rs_1("nsort1")
case "article"
strSql="select 1 from article where charindex('"&rs_1("filename")&"',word)>0"
case "forum"
strSql="select 1 from bbs_data where charindex('"&rs_1("filename")&"',word)>0"
Case Else
strSql=""
end select
If strSql<>"" then
set rs=server.createobject("adodb.recordset")
rs.open strSql,conn,1,1
If rs.RecordCount>0 Then
strSql=""
Else
conn.execute("update upload set iid=0,types=0 where id="&rs_1("id"))
End If
rs.close:set rs=Nothing
End if
rs_1.movenext
loop
rs_1.close:set rs_1=Nothing
end if
%>
as
declare @id int
declare @file varchar(100),@ns varchar(100)
declare cur cursor for
select id,nsort1,filename from upload where id>1 and id<=1000 order by id
open cur
fetch next from cur into @id,@ns,@file
while @@fetch_status=0
begin
if @ns='article' or @ns='forum'
begin
exec('select 1 from article where charindex('''+@file+''',word)>0')
if @@rowcount=0
update upload set iid=0,types=0 where id=@id
end
fetch next from cur into @id,@ns,@file
end
close cur
deallocate cur