[Q] Can I convert this ASP code to Stored Procedur

Results 1 to 3 of 3

Thread: [Q] Can I convert this ASP code to Stored Procedur

  1. #1
    Ed Lee Guest

    Default [Q] Can I convert this ASP code to Stored Procedur

    Hi, <BR><BR>I am trying to make a stored procedure for SQL Server 7.0. <BR><BR>I want this SP to look up records and update them as needed. I wrote ASP code to do it, but I want to make it SP in order to speed the process up. However, I have no idea how to use loop in the SP. <BR><BR>I included the code, so can somebody advise me how to convert this to SP? OR, at least can anyone tell me whether it&#039;s possible to convert it to SP at all?<BR><BR>PLEASE HELP ME!!<BR><BR>***** <BR><BR><BR>Dim rs, rc <BR><BR><BR>set rs=conn1.execute("select schoolid, schoolname from one_h") <BR><BR>do while rs.eof=false <BR><BR> Dim schoolid, schoolname, name, oneq, twoq, totalqone, totalqtwo, numstudent <BR><BR> <BR> schoolid = rs("schoolid") <BR> schoolname = rs("schoolname") <BR><BR> numstudent = 0 <BR> totaliqone = 0 <BR> totaliqtwo = 0 <BR><BR> set rc=conn1.execute("select name, oneq, twoq from member where schoolstatus = &#039;" & "ABC" & "&#039; AND schoolid = &#039;" & schoolid & "&#039;") <BR> <BR> do while rc.eof=false <BR> <BR> numstudent = numstudent + 1 <BR> totaliqone = rc("oneq") + totaliqone <BR> totaliqtwo = rc("twoq") + totaliqtwo <BR> <BR> rc.movenext <BR> loop <BR> <BR> rc.close <BR> set rc = nothing <BR><BR> if numstudent &#060;&#062; 0 then <BR> <BR> Dim SQL <BR> <BR> SQL = "Update one_h set numstudent = &#039;" & numstudent & "&#039;, totaliqone = &#039;" & totaliqone & "&#039;, totaliqtwo = &#039;" & totaliqtwo & "&#039; where schoolid = &#039;" & schoolid & "&#039;" <BR> conn1.execute(SQL) <BR><BR> <BR>rs.movenext <BR>loop <BR><BR>rs.close <BR>set rs=nothing <BR>

  2. #2
    Join Date
    Dec 1969

    Default Crosspost. <eop>


  3. #3
    Join Date
    Dec 1969

    Default Try using cursors

    One solution you might want to try using database cursor. The following example is written for Microsoft SQL Server. You might want to read up on cursors to find out some of the declarations and what else you can do with it. <BR>--------------------------------------------- <BR><BR>Create Procedure upd_one_h <BR><BR>AS <BR><BR>DECLARE <BR>@schoolid varchar (20), <BR>@numstudent varchar (20), <BR>@totaliqone varchar (20), <BR>@totaliqtwo varchar (20) <BR><BR><BR><BR>DECLARE cur_one_h CURSOR Local FAST_FORWARD <BR>FOR SELECT schoolid FROM one_h <BR><BR>Open cur_one_h <BR>Fetch Next From cur_one_h Into @schoolid <BR>While @@Fetch_Status = 0 <BR>BEGIN <BR>SELECT @numstudent = convert(varchar(20),count(*)), <BR>@totaliqone = convert(varchar(20),sum(convert(numeric,oneq))), <BR>@totaliqtwo = convert(varchar(20),sum(convert(numeric,twoq))) <BR>FROM member WHERE schoolstatus = &#039;ABC&#039; AND schoolid = @schoolid <BR><BR>UPDATE one_h <BR>SET numstudent = @numstudent, totaliqone = @totaliqone, totaliqtwo = @totaliqtwo <BR>WHERE schoolid = @schoolid <BR><BR>Fetch Next From cur_one_h Into @BrBPONum, @BusPartnerNum <BR>END <BR>DeAllocate cur_one_h <BR>GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts