各种存储过程使用指南
程序员文章站
2022-08-06 17:23:20
<% '---开始链接数据库 dim strconnstring strconnstring = "driver={sql s...
<%
'---开始链接数据库
dim strconnstring
strconnstring = "driver={sql server};server=songhp;uid=sa;pwd=;database=xvzdemo"
set conn = server.createobject("adodb.connection")
conn.open strconnstring
'---结束链接数据库
'---开始为输入参数赋值
dim selectsql , selectrs
dim selectid , selectname , selectreturn
selectsql = "select max(companyid) from dim_company"
set selectrs = conn.execute(selectsql)
selectid = selectrs(0)
'---结束为输入参数赋值
dim tiggertype
tiggertype = 3
set cmd = server.createobject("adodb.command")
set cmd.activeconnection = conn
cmd.commandtype = 4 '---声明此过程为存储过程
if tiggertype = 1 then
'---开始一个输入参数的存储过程调用
cmd.commandtext = "transcompany1"
set cmdparam = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparam
cmd("@transid") = selectid
cmd.execute
'---结束一个输入参数的存储过程调用
elseif tiggertype = 2 then
'---开始一个输入参数,一个输出参数的存储过程调用
cmd.commandtext = "transcompany2"
set cmdparamid = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparamid
cmd("@transid") = selectid
set cmdparamname = cmd.createparameter("@transname",202,2,50)
cmd.parameters.append cmdparamname
cmd.execute
selectname = cmd("@transname")
'---结束一个输入参数,一个输出参数的存储过程调用
elseif tiggertype = 3 then
'---开始一个输入参数,一个输出参数,一个返回值的存储过程调用
cmd.commandtext = "transcompany3"
set cmdparamreturn = cmd.createparameter("return_value",3,4)
cmd.parameters.append cmdparamreturn
set cmdparamid = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparamid
cmd("@transid") = selectid
set cmdparamname = cmd.createparameter("@transname",202,2,50)
cmd.parameters.append cmdparamname
cmd.execute
selectname = cmd("@transname")
selectreturn = cmd("return_value")
'---结束一个输入参数,一个输出参数,一个返回值的存储过程调用
end if
conn.close
set conn = nothing
set cmd = nothing
set cmdparamid = nothing
set cmdparamname = nothing
set cmdparamreturn = nothing
%>
'---开始链接数据库
dim strconnstring
strconnstring = "driver={sql server};server=songhp;uid=sa;pwd=;database=xvzdemo"
set conn = server.createobject("adodb.connection")
conn.open strconnstring
'---结束链接数据库
'---开始为输入参数赋值
dim selectsql , selectrs
dim selectid , selectname , selectreturn
selectsql = "select max(companyid) from dim_company"
set selectrs = conn.execute(selectsql)
selectid = selectrs(0)
'---结束为输入参数赋值
dim tiggertype
tiggertype = 3
set cmd = server.createobject("adodb.command")
set cmd.activeconnection = conn
cmd.commandtype = 4 '---声明此过程为存储过程
if tiggertype = 1 then
'---开始一个输入参数的存储过程调用
cmd.commandtext = "transcompany1"
set cmdparam = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparam
cmd("@transid") = selectid
cmd.execute
'---结束一个输入参数的存储过程调用
elseif tiggertype = 2 then
'---开始一个输入参数,一个输出参数的存储过程调用
cmd.commandtext = "transcompany2"
set cmdparamid = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparamid
cmd("@transid") = selectid
set cmdparamname = cmd.createparameter("@transname",202,2,50)
cmd.parameters.append cmdparamname
cmd.execute
selectname = cmd("@transname")
'---结束一个输入参数,一个输出参数的存储过程调用
elseif tiggertype = 3 then
'---开始一个输入参数,一个输出参数,一个返回值的存储过程调用
cmd.commandtext = "transcompany3"
set cmdparamreturn = cmd.createparameter("return_value",3,4)
cmd.parameters.append cmdparamreturn
set cmdparamid = cmd.createparameter("@transid",3,1)
cmd.parameters.append cmdparamid
cmd("@transid") = selectid
set cmdparamname = cmd.createparameter("@transname",202,2,50)
cmd.parameters.append cmdparamname
cmd.execute
selectname = cmd("@transname")
selectreturn = cmd("return_value")
'---结束一个输入参数,一个输出参数,一个返回值的存储过程调用
end if
conn.close
set conn = nothing
set cmd = nothing
set cmdparamid = nothing
set cmdparamname = nothing
set cmdparamreturn = nothing
%>