一個簡單的ASP執行OUTPUT和RETURN型的存儲過程

1,表的結構如下:

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[sbit] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ sbit ]
GO

CREATE   TABLE   [ dbo ] . [ sbit ]  (
     [ id ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ sbit ]   [ char ]  ( 1 ) COLLATE Chinese_Taiwan_Stroke_CI_AS  NULL  ,
     [ schar ]   [ char ]  ( 1 ) COLLATE Chinese_Taiwan_Stroke_CI_AS  NULL  
)  ON   [ PRIMARY ]
GO

ALTER   TABLE   [ dbo ] . [ sbit ]   WITH   NOCHECK   ADD  
     CONSTRAINT   [ PK_sbit ]   PRIMARY   KEY    CLUSTERED  
    (
         [ id ]
    )   ON   [ PRIMARY ]  
GO

2,存儲過程
2.1帶有兩個OUTPUT參數

CREATE   PROCEDURE  sp_sbit_list
@sbit     char ( 1 ),
@num      int = 0  output,
@num_1    int = 0  output
AS
set  nocount  on
Declare   @str   varchar ( 1000 )
set   @str = ' select * from sbit '
if   @sbit <> ''   set   @str = @str   +   '  where sbit= '   +   @sbit   +   ''
set   @str = @str   +   '  order by sbit '
exec ( @str )
set   @num = @@ROWCOUNT
set   @num_1 = @num + 1
set  nocount  off
GO

3,相關的兩個ASP文件,一個是UTY.ASP內有一個連接DB的FUNCTION另外一個adovbs.inc里面是一些常量的定議,兩個文件都在根目錄下的UTY子目錄下,兩個文件的內容如下:
UTY.ASP

< %
strIp = " 192.168.1.35 "
Function  Opendb(conn_to)
    Application.Lock
     Dim  conn
     Set  conn  =  Server.CreateObject( " ADODB.Connection " )
    conn.ConnectionTimeout  =   50
    conn.CommandTimeout  =   120
     if   UCase (conn_to) = " MSC "   then
    connString  =   " Driver={sql server};uid=mscuser;pwd=;database=prs_msc;server= " + strIP
     else
       ' connString = "Driver={sql server};uid=apuser;pwd=prosperous;database=emsc;server="+strIP
      connString  =   " Driver={sql server};uid=sa;pwd=sa;database=DB_T;server=(local) "
   end   if
  conn.open connString
     Set  Opendb  =  conn
    Application.UnLock
End Function

% >

adovbs.inc:

< %
' ----常用到的
' ---- DataTypeEnum Values ----
Const  adEmpty  =   0
Const  adTinyInt  =   16
Const  adSmallInt  =   2
Const  adInteger  =   3
Const  adBigInt  =   20
Const  adUnsignedTinyInt  =   17
Const  adUnsignedSmallInt  =   18
Const  adUnsignedInt  =   19
Const  adUnsignedBigInt  =   21
Const  adSingle  =   4
Const  adDouble  =   5
Const  adCurrency  =   6
Const  adDecimal  =   14
Const  adNumeric  =   131
Const  adBoolean  =   11
Const  adError  =   10
Const  adUserDefined  =   132
Const  adVariant  =   12
Const  adIDispatch  =   9
Const  adIUnknown  =   13
Const  adGUID  =   72
Const  adDate  =   7
Const  adDBDate  =   133
Const  adDBTime  =   134
Const  adDBTimeStamp  =   135
Const  adBSTR  =   8
Const  adChar  =   129
Const  adVarChar  =   200
Const  adLongVarChar  =   201
Const  adWChar  =   130
Const  adVarWChar  =   202
Const  adLongVarWChar  =   203
Const  adBinary  =   128
Const  adVarBinary  =   204
Const  adLongVarBinary  =   205
Const  adChapter  =   136
Const  adFileTime  =   64
Const  adPropVariant  =   138
Const  adVarNumeric  =   139
Const  adArray  =   & H2000
' ---- ParameterDirectionEnum Values ----
Const  adParamUnknown  =   & H0000
Const  adParamInput  =   & H0001
Const  adParamOutput  =   & H0002
Const  adParamInputOutput  =   & H0003
Const  adParamReturnValue  =   & H0004
' ---- CommandTypeEnum Values ----
Const  adCmdUnknown  =   & H0008
Const  adCmdText  =   & H0001
Const  adCmdTable  =   & H0002
Const  adCmdStoredProc  =   & H0004
Const  adCmdFile  =   & H0100
Const  adCmdTableDirect  =   & H0200
% >

4,運行時的ASP文件01.ASP

< %@LANGUAGE = " VBSCRIPT "  CODEPAGE = " 950 " % >
< ! --  #include file = " uty/adovbs.inc " -->
< ! --  #include file = " uty/uty.asp " -->
< html >
< head >
< meta http - equiv = " Content-Type "  content = " text/html; charset=big5 " >
< title > 執行帶有output返回參數的存儲過程 </ title >
</ head >

< body >
< %
set  conn = opendb( " DB_T " )
dim  objCnn
dim  objCmd
sbit = ""

set  objCmd = Server.CreateObject( " Adodb.Command " )
Set  rs  =  Server.CreateObject( " ADODB.Recordset " )
objCmd.ActiveConnection = conn
objCmd.CommandText = " sp_sbit_list "  
objCmd.CommandType = adCmdStoredProc 
objCmd.Prepared  =   true  

objCmd.Parameters.Append _
objCmd.CreateParameter( " sbit " ,adChar,adParamInput, 1 ,sbit)
objCmd.Parameters.Append _
objCmd.CreateParameter( " num " ,adInteger,adParamOutput, 4 , 0 )
objCmd.Parameters.Append _
objCmd.CreateParameter( " num_1 " ,adInteger,adParamOutput, 4 , 0 )

set  rs = objCmd.Execute
rs.close()
response.Write(objCmd( 1 )  &   " <br> "   &  objCmd( 2 ))
' 打印出所有的參數值
' for each parm in objCmd.Parameters
' Response.Write parm.name &"="& trim(parm) &"<br>"
' next
set  objCmd = nothing
response.Write( " <br> " )
rs.open

while   not  rs.eof
  response.Write(rs( 0 )  &   " --- "   &  rs( 1 )  &   " --- "   &  rs( 2 ))
    response.Write( " <br> " )
    rs.movenext
wend

rs.close(): set  rs = nothing
conn.close(): set  conn = nothing
% >
</ body >
</ html >

2.2(執行帶有RETURN和OUTPUT的存儲過程)

CREATE   PROCEDURE  sp_sbit_list
@sbit     char ( 1 ),
@num      int = 0  output,
@num_1    int = 0  output
AS
set  nocount  on
Declare   @str   varchar ( 1000 )
set   @str = ' select * from sbit '
if   @sbit <> ''   set   @str = @str   +   '  where sbit= '   +   @sbit   +   ''
set   @str = @str   +   '  order by sbit '
exec ( @str )
set   @num = @@ROWCOUNT
set   @num_1 = @num + 1
if   @num > 0   return   1   else   return   0
set  nocount  off
GO

5,執行的ASP文件內容如下:

< %@LANGUAGE = " VBSCRIPT "  CODEPAGE = " 950 " % >
< ! --  #include file = " uty/adovbs.inc " -->
< ! --  #include file = " uty/uty.asp " -->
< html >
< head >
< meta http - equiv = " Content-Type "  content = " text/html; charset=big5 " >
< title > 執行帶有output返回參數的存儲過程 </ title >
</ head >

< body >
< %
set  conn = opendb( " DB_T " )
dim  objCnn
dim  objCmd
sbit = ""

set  objCmd = Server.CreateObject( " Adodb.Command " )
Set  rs  =  Server.CreateObject( " ADODB.Recordset " )
objCmd.ActiveConnection = conn
objCmd.CommandText = " sp_sbit_list "  
objCmd.CommandType = adCmdStoredProc 
objCmd.Prepared  =   true  

objCmd.Parameters.Append _
objCmd.CreateParameter( " return " ,adInteger,adParamReturnValue)
objCmd.Parameters.Append _
objCmd.CreateParameter( " sbit " ,adChar,adParamInput, 1 ,sbit)
objCmd.Parameters.Append _
objCmd.CreateParameter( " num " ,adInteger,adParamOutput, 4 , 0 )
objCmd.Parameters.Append _
objCmd.CreateParameter( " num_1 " ,adInteger,adParamOutput, 4 , 0 )

set  rs = objCmd.Execute
rs.close()
response.write( " RETURN: "   &  objCmd( 0 ) )
response.write( " <br> " )
response.Write( " OUTPUT: "   &  objCmd( 2 )  &   " & "   &  objCmd( 3 ))
' 打印出所有的參數值,如果有return是必須放在參數的最前面,接收值的時候,如果有return
' 第一個的值也是return也就是objcmd(0),接下來的就是第二個參數,第三個.
' 所以上面的兩個output參數應該是objcmd(2) objcmd(3)
' for each parm in objCmd.Parameters
'   Response.Write parm.name &"="& trim(parm) &"<br>"
' next
set  objCmd = nothing
response.Write( " <br> " )
rs.open

while   not  rs.eof
  response.Write(rs( 0 )  &   " --- "   &  rs( 1 )  &   " --- "   &  rs( 2 ))
    response.Write( " <br> " )
    rs.movenext
wend

rs.close(): set  rs = nothing
conn.close(): set  conn = nothing
% >
</ body >
</ html >

下面有一种簡化寫法如下:

< %@LANGUAGE = " VBSCRIPT "  CODEPAGE = " 950 " % >
< ! --  #include file = " uty/adovbs.inc " -->
< ! --  #include file = " uty/uty.asp " -->
< html >
< head >
< meta http - equiv = " Content-Type "  content = " text/html; charset=big5 " >
< title > 執行帶有output返回參數的存儲過程 </ title >
</ head >

< body >
< %
set  conn = opendb( " DB_T " )
dim  objCnn
dim  objCmd
sbit = ""

Set  rs  =  Server.CreateObject( " ADODB.Recordset " )
set  objCmd = Server.CreateObject( " Adodb.Command " )
with  objCmd
  .ActiveConnection  =  conn                ' conn是聯接數據庫字符串
  .CommandText       =   " sp_sbit_list "       ' 存儲過程名稱
  .CommandType       =  adCmdStoredProc     ' 說明這是一個存儲過程
  .Prepared          =   true                 ' 要求要命預編譯
  .Parameters.append .CreateParameter( " @return " ,adInteger,adParamReturnValue)
  .Parameters.append .CreateParameter( " @sbit " ,adChar,adParamInput, 1 ,sbit)
  .Parameters.append .CreateParameter( " @num " ,adInteger,adParamOutput, 4 , 0 )
  .Parameters.append .CreateParameter( " @num_1 " ,adInteger,adParamOutput, 4 , 0 )
   Set  rs = .Execute
end   with
rs.close()
response.write( " RETURN: "   &  objCmd( 0 ) )
response.write( " <br> " )
response.Write( " OUTPUT: "   &  objCmd( 2 )  &   " & "   &  objCmd( 3 ))
' 打印出所有的參數值,如果有return是必須放在參數的最前面,接收值的時候,如果有return
' 第一個的值也是return也就是objcmd(0),接下來的就是第二個參數,第三個.
' 所以上面的兩個output參數應該是objcmd(2) objcmd(3)
' for each parm in objCmd.Parameters
'   Response.Write parm.name &"="& trim(parm) &"<br>"
' next
set  objCmd = nothing
response.Write( " <br> " )
rs.open

while   not  rs.eof
  response.Write(rs( 0 )  &   " --- "   &  rs( 1 )  &   " --- "   &  rs( 2 ))
    response.Write( " <br> " )
    rs.movenext
wend

rs.close(): set  rs = nothing
conn.close(): set  conn = nothing
% >
</ body >
</ html >

在添加參數的時候有"@"和沒有都可以,原則上最好是有較規範.