把一个asp页面代码改成存储过程实现（第一天上班的任务）

最新推荐文章于 2024-12-07 09:45:00 发布

转载最新推荐文章于 2024-12-07 09:45:00 发布 · 115 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/weipengyang/archive/2005/08/31/227391.html

原来的asp代码如下：
<% if trim(request("cardid"))="" or trim(request("ktype"))="" or trim(request("jihao"))="" or trim(request("Kdate"))="" or trim(request("Ktime"))="" then
   Response.Write("www.169.comcount=error")
   response.end
   else
   dim Count8,Card8,type8,jihao8,date8,time8,int1
   count8=clng(trim(request("count")))
   Card8=trim(request("cardid"))
   type8=trim(request("ktype"))
   jihao8=trim(request("jihao"))
   date8=trim(request("Kdate"))
   time8=trim(request("Ktime"))
   Card8=Split(Card8,",")
   date8=Split(date8,",")
   time8=Split(time8,",")
   type8=Split(type8,",")
   for int1=0 to count8-1
   if Card8(int1)="" or date8(int1)="" or time8(int1)="" or type8(int1)="" then Exit For
   next

   if int1<>count8 then
   Response.Write("www.169.comcount=error")
   response.end
   else
   dim conn,jobb,dtime,ddate1,int,ddate2,ddate3,ddate,dtime1,dtime2
   set conn=server.CreateObject("adodb.connection")
   'conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" &Server.MapPath("data/kaoqin.mdb")
   conn.Open connstr
   set jobb=server.CreateObject("adodb.recordset")
   set jobb.activeconnection=conn
   jobb.open "select * from card",,1,3
   for int=0 to clng(trim(request("count")))-1
       ddate=cdate("20"&left(date8(int),2)&"-"&left(right(date8(int),4),2)&"-"&right(date8(int),2))
       dtime=cdate(left(time8(int),2)&":"&right(time8(int),2))
       conn.execute "insert into card(gzkh,date8,time8,type,coding)values("&cdbl(card8(int))&",'"&ddate&"','"&dtime&"','"&type8(int)&"',"&Clng(trim(request("jihao")))&")"
   next
   jobb.close
   conn.close
   end if
   end if
   Response.Write "www.169.comcount="&trim(request("count"))&""
   response.end %>
存储过程如下：
create proc pr_insert
(@count int,
@cardid nvarchar(200),
@datetime nvarchar(200),
@mjihao nvarchar(200),
@cjihao nvarchar(200),
@status nvarchar(200)
)
as
declare @kh int,@date8 nvarchar(200),@time8 nvarchar(200),@mjjbh int,@mjkzqbh int,@syqk nvarchar(200),@crjqk nvarchar(200)
declare @intcardid int, @intdatetime int, @intmjihao int ,@intstatus int,@int1 int
set @int1=0
while(@int1<=@count-1)
begin

Set @intcardid = CharIndex(',',@cardid,0) --分割卡号
if(@intcardid>0)
begin
set @kh=Left(@cardid,@intcardid-1)
Set @cardid = substring(@cardid,@intcardid+1,Len(@cardid))
end

else

begin
set @kh=@cardid
end
Set @intdatetime = CharIndex(',',@datetime,0) --分割时间

declare @Dt char(50)

if(@intdatetime>0)
begin

set @Dt=Left(@datetime,@intdatetime-1)
Set @datetime = substring(@datetime,@intdatetime+1,Len(@datetime))
set @date8=left(@Dt,6)
set @date8='20'+left(@date8,2)+'-'+right(left(@date8,4),2)+'-'+right(@date8,2)
set @time8=substring(@Dt,7,10)
set @time8=left(@time8,2)+':'+substring(@time8,3,7)
end

else
begin
set @Dt=@datetime
set @date8=left(@Dt,6)
set @date8='20'+left(@date8,2)+'-'+right(left(@date8,4),2)+'-'+right(@date8,2)
set @time8=substring(@Dt,7,10)
set @time8=left(@time8,2)+':'+substring(@time8,3,7)
end

Set @intmjihao = CharIndex(',',@mjihao,0) --分割门禁号
if(@intmjihao>0)
begin
set @mjjbh=cast(Left(@mjihao,@intmjihao-1) as int )
Set @mjihao = substring(@mjihao,@intmjihao+1,Len(@mjihao))

end

else

begin
set @mjjbh=cast(@mjihao as int)
end

Set @intstatus = CharIndex(',',@status,0)
if(@intstatus>0)
begin
set @syqk=cast((left(@status,@intstatus-1)/10)as int)
set @crjqk=left(@status,@intstatus-1) % 10
Set @status = substring(@status,@intstatus+1,Len(@status))
end

else

begin
set @syqk=cast((@status/10)as int)
set @crjqk=@status % 10
end

set @int1=@int1+1
set @mjkzqbh=cast(@cjihao as int)
insert into crjsj(kh,date8,time8,mjjbh,mjkzqbh,syqk,crjqk)values(@kh,@date8 ,@time8 ,@mjjbh,@mjkzqbh,@syqk,@crjqk)

end

execute pr_insert @count=3,@cardid='200001,200002,200003',@datetime= '0506140812,0512030716,4532364645',@mjihao='6,4,8',@cjihao='2',@status='30,50,67'

其实还有一种改写方法就是用临时表，这样会使存储过程简单更清晰化，经我测试，一点都不影响效率，具体方法如下：
create proc pr_insert
(@count int,
@cardid nvarchar(200),
@datetime nvarchar(200),
@mjihao char(20),
@cjihao char(20),
@status nvarchar(200)
)
as

declare @kh int,@date8 nvarchar(200),@time8 nvarchar(200),@mjjbh int,@mjkzqbh int,@syqk nvarchar(200),@crjqk nvarchar(200)
declare @sql1 varchar(1000),@sql2 varchar(1000),@sql3 varchar(1000),@sql4 varchar(1000)
create table #table1 (ID bigint IDENTITY (1, 1) NOT NULL,cardid int)
set @sql1='insert into #table1(cardid) select '+replace(@cardid,',',' col union all select ')
exec(@sql1)
create table #table2 (ID bigint IDENTITY (1, 1) NOT NULL ,datatime varchar(200))
set @sql2='insert into #table2(datatime) select '+replace(@datetime,',',' col union all select ')

exec(@sql2)
create table #table3 (ID bigint IDENTITY (1, 1) NOT NULL ,mjihao int)
set @sql3='insert into #table3(mjihao) select '+replace(@mjihao,',',' col union all select ')
exec(@sql3)
create table #table4 (ID bigint IDENTITY (1, 1) NOT NULL ,status int)
set @sql4='insert into #table4(status) select '+replace(@status,',',' col union all select ')
exec(@sql4)

declare @int1 int
set @int1=1
while(@int1<=@count)

begin

declare @strdate nvarchar(50)
set @strdate=(select datatime from #table2 where id=@int1)
if(len(@strdate)<10)
begin
set @strdate='0'+@strdate
end
set @syqk=((select status from #table4 where id=@int1)/10)
set @crjqk=(select status from #table4 where id=@int1) % 10
set @date8=left(@strdate,6)
set @date8='20'+left(@date8,2)+'-'+right(left(@date8,4),2)+'-'+right(@date8,2)
set @time8=right(@strdate,4)
set @time8=left(@time8,2)+':'+substring(@time8,3,7)

--set @time8=left(@time8,2)+':'+right(@time8,2)
set @kh=(select cardid from #table1 where id=@int1)
set @mjjbh=(select mjihao from #table3 where id=@int1)
set @mjkzqbh=@cjihao
set @int1=@int1+1
insert into crjsj(kh,date8,time8,mjjbh,mjkzqbh,syqk,crjqk)values(@kh,@date8,@time8,@mjjbh,@mjkzqbh,@syqk,@crjqk)

end

下面我就直接在asp中调用就ok了
set conn=server.CreateObject("adodb.connection")
conn.Open connstr
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=conn
objCmd.CommandText="pr_insert" '指定存储过程名称
objCmd.CommandType=4 '其为Stored Procedure
'-----准备stored procedure 的参数-------“count/cardid/datetime/mjihao/cjihao/status 为你的输入参数值”
objCmd.Parameters.Append objCmd.CreateParameter("@count",3,1,,count)
objCmd.Parameters.Append objCmd.CreateParameter("@cardid",200,1,200,cardid)
objCmd.Parameters.Append objCmd.CreateParameter("@datetime",200,1,200,datetime)
objCmd.Parameters.Append objCmd.CreateParameter("@mjihao",200,1,20,mjihao)
objCmd.Parameters.Append objCmd.CreateParameter("@cjihao",200,1,20,cjihao)
objCmd.Parameters.Append objCmd.CreateParameter("@status",200,1,200,status)

'-----执行存储过程----------------------
objCmd.Execute
'conn.Execute "pr_insert '"&count&"','"&cardid&"','"&datetime&"','"&mjihao&"','"&cjihao&"','"&status&"'"

转载于:https://www.cnblogs.com/weipengyang/archive/2005/08/31/227391.html