自己写的带分页的存储过程

储存过程

CREATE  PROCEDURE dbo.select_my
       @Num varchar(20),
       @User varchar(40),
       @pagenum int,
       @countt int output
    as
    set nocount on
   if @User is null
 SELECT @countt=count(id) FROM [招聘信息]
   else
    SELECT @countt=count(id) FROM [招聘信息] WHERE  招聘职位 like '%'+CAST(@User AS VARCHAR(40))+'%'

   --@countt=CAST(@countt AS int)
   DECLARE @Str nVARCHAR(4000)

   if @User is null
     SET @Str='SELECT TOP '+ CAST(@pagenum AS VARCHAR(20))+' * FROM [招聘信息] WHERE (id NOT IN (SELECT TOP '+ CAST(@NUm AS VARCHAR(20))+ ' id FROM [招聘信息]  ORDER BY id desc)) ORDER BY id desc'
   else
    SET @Str='SELECT TOP '+ CAST(@pagenum AS VARCHAR(20))+' * FROM [招聘信息] WHERE  招聘职位 like ''%'+ CAST(@User AS VARCHAR(40))+'%'' and (id NOT IN (SELECT TOP '+ CAST(@NUm AS VARCHAR(20))+ ' id FROM [招聘信息] where 招聘职位 like ''%'+CAST(@User AS VARCHAR(40))+'%'' ORDER BY id desc)) ORDER BY id desc'

    print @Str
    EXEC sp_ExecuteSql @Str
 --  if @@rowcount>0
 --        return @@rowcount
 --      else
 --        return 0

   

GO

---------------------------------------------------------------------------------

22.asp

<%textfield=Trim(Request("textfield"))
%>
<form name="form1" method="post" action="22.asp">
  关键字：
  <input type="text" name="textfield" value="<%=textfield%>">
  <input type="submit" name="Submit" value="提交">
</form>
<%
 Set MyConStr = Server.CreateObject("ADODB.Connection")
 DSNtemp="DRIVER={SQL Server};SERVER=192.168.0.69;UID=sa;PWD=zhangff;DATABASE=cnfda1"
 MyConStr.Open DSNtemp
 page=(Request.QueryString("page"))
 DIM MyComm,UserID,UserName
 pagenum=10
 if (IsNumeric(page) and page<0) or (not IsNumeric(page)) or page="" then
  page=0
 end if
 UserID=pagenum*page
 
 if textfield="" then
    UserName = null
 else
 UserName=textfield
 end if
    Set MyComm = Server.CreateObject("ADODB.Command")
    'for UserID = 1 to 2
       with MyComm
          .ActiveConnection = MyConStr          'MyConStr是数据库连接字串
          .CommandText      = "select_my"     '指定存储过程名
          .CommandType      = 4                 '表明这是一个存储过程
          .Prepared         = true              '要求将SQL命令先行编译
     '.Parameters.Append .CreateParameter("RETURN",2,4)
             .Parameters.append .CreateParameter("@Num",3,1,4,UserID)
             .Parameters.append .CreateParameter("@User",200,1,40,UserName)
    .Parameters.append .CreateParameter("@pagenum",3,1,4,pagenum)
   .Parameters.append .CreateParameter("@countt",3,2,4)
    '.Parameters.append .CreateParameter("@RETURN_VALUE",2,4)
             .Execute
          '  DD = .Parameters("@countt").Value
     DD = .Parameters("@countt").Value
       end with
    set rs=MyComm.execute
   'DD = MyComm.Parameters("@countt").Value
       'UserName = UserName + MyComm(1) + ","    '也许你喜欢用数组存储
    'next
 'Response.Write(UserName)
    'Set MyComm = Nothing
 'Response.Write(DD&"<br>")
%>
<table width="100%" border="0" cellpadding="0" cellspacing="1" bgcolor="#000000">
  <tr bgcolor="#FFFFFF">
    <td colspan="2">共有<%=dd%>个数据</td>
  </tr>
    <tr bgcolor="#FFFFFF">
    <td colspan="2">&nbsp;</td>
  </tr>
  <%
while not  rs.eof
%>
  <tr bgcolor="#FFFFFF">
    <td width="27" height="30" align="center" valign="middle"><%=rs(0)%></td>
    <td width="963">&nbsp;&nbsp;<a href="hhh.asp?id=<%=rs(0)%>"><%=rs(1)%></a></td>
  </tr>
  <%
rs.movenext
wend
%>
  <tr bgcolor="#FFFFFF">
    <td colspan="2"><%
 for i=0 to (dd/pagenum)
 if cint(page)<>i then
 Response.Write("&nbsp;&nbsp;<a href=?page="&i&"&textfield="&textfield&">")
 Response.Write((i+1))
 Response.Write("</a>")
 else
 Response.Write("&nbsp;&nbsp;<font color=ff1000><b>"&(i+1)&"</b></font>")
 end if
 next
 %></td>
  </tr>
</table>
<%
rs.close
set rs=nothing

MyComm.Parameters.delete 3
MyComm.Parameters.delete 2
MyComm.Parameters.delete 1
MyComm.Parameters.delete 0

set MyComm=nothing

MyConStr.close
set MyConStr=nothing
 'Response.Write(UserName)
%>