一個簡單的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"%>


<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) & " " & objCmd(2))
'打印出所有的參數值
'for each parm in objCmd.Parameters
'Response.Write parm.name &"="& trim(parm) &" "
'next
set objCmd=nothing
response.Write(" ")
rs.open

while not rs.eof
 response.Write(rs(0) & "---" & rs(1) & "---" & rs(2))
 response.Write(" ")
 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"%>


<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(" ")
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) &" "
'next
set objCmd=nothing
response.Write(" ")
rs.open

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

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

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

<%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%>


<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(" ")
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) &" "
'next
set objCmd=nothing
response.Write(" ")
rs.open

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

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

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

申明

非源创博文中的内容均收集自网上，若有侵权之处，请及时联络，我会在第一时间内删除．再次说声抱歉!!!

博文欢迎转载，但请给出原文连接。

相关阅读:
跑酷游戏的一些bug总结(滥用FixedUpdate的坑)
Unity在编辑器状态下清空控制台信息
Unity脚本在层级面板中的执行顺序测试3
IronPython使用
RSA加密的测试demo
常用加密算法学习
c#读写ini文件
Jrebel激活方法(转)
ThreadLocal Memory Leak in Java web application
Java Thread Local – How to use and code sample（转）

原文地址：https://www.cnblogs.com/Athrun/p/973761.html