ASP 两个小的存储过程

(无参数)

 sql:create procedure dbo.getuserlist
as
set nocount on
begin
  select * from t_bill
end
go

 

<%
serverName="192.168.0.7"
dbName="play_data1"
saName="sa"
saPass="1"
dim mycomm,myrst
  set mycomm=server.createobject("adodb.Connection")
  application("connstr")="provider=SQLOLEDB;Data Source =" & serverName & ";database=" & dbName & ";user id=" & saName & "; password=" & saPass
  mycomm.open application("connstr")
  'mycomm.CommandText = "getuserlist"
  'mycomm.commandType = 4
 ' mycomm.Prepared= true
  set myrst=mycomm.Execute("getuserlist",0,4)
  set mycomm=Nothing
  do while not myrst.eof
  response.Write(myrst("ID")&"<br>")
 
  myrst.movenext
  loop
%>

 

 

（2）有参数

CREATE PROCEDURE dbo.getUserName
       @UserID int,
       @UserName varchar(40) output
    as
    set nocount on
    begin
       if @UserID is null return
       select @UserName=UserName
           from dbo.[User]
           where Id=@UserID
       return
    end
    go

 

<%
serverName="192.168.0.7"
dbName="XshForum"
saName="sa"
saPass="1"
DIM MyComm,UserID,UserName
    UserID = 1
    Set MyComm = Server.CreateObject("ADODB.Command")
 MyConStr="provider=SQLOLEDB;Data Source =" & serverName & ";database=" & dbName & ";user id=" & saName & "; password=" & saPass
    MyComm.ActiveConnection = MyConStr          'MyConStr是数据库连接字串
    MyComm.CommandText      = "getUserName"     '指定存储过程名
    MyComm.CommandType      = 4                 '表明这是一个存储过程
    MyComm.Prepared         = true             '要求将SQL命令先行编译
    '声明参数
    MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
    MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)
    MyComm.Execute
    '取得出参
    UserName = MyComm(1)
    Set MyComm = Nothing
 response.Write(UserName)
%>