各种存储过程使用指南

1@ Language=VBScript 
 1 
 2
 3'---开始链接数据库   
 4Dim strConnString   
 5strConnString = "driver={SQL Server};server=songhp;uid=sa;pwd=;database=XVZDemo"   
 6set Conn = Server.CreateObject("ADODB.Connection")   
 7Conn.Open strConnstring   
 8'---结束链接数据库 
 9
10'---开始为输入参数赋值   
11Dim SelectSql , SelectRs   
12Dim SelectID , SelectName , SelectReturn   
13SelectSql = "Select Max(CompanyID) From Dim_Company"   
14Set SelectRs = Conn.Execute(SelectSql)   
15SelectID = SelectRs(0)   
16'---结束为输入参数赋值 
17
18Dim TiggerType   
19TiggerType = 3 
20
21Set Cmd = Server.CreateObject("ADODB.Command")   
22Set Cmd.ActiveConnection = Conn   
23Cmd.CommandType = 4 '---声明此过程为存储过程 
24
25If TiggerType = 1 then   
26  
27'---开始一个输入参数的存储过程调用   
28Cmd.CommandText = "TransCompany1"   
29  
30Set CmdParam = Cmd.CreateParameter("@TransID",3,1)   
31Cmd.Parameters.Append CmdParam   
32Cmd("@TransID") = SelectID   
33Cmd.Execute   
34'---结束一个输入参数的存储过程调用   
35  
36Elseif TiggerType = 2 then 
37
38'---开始一个输入参数,一个输出参数的存储过程调用   
39Cmd.CommandText = "TransCompany2"   
40  
41Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)   
42Cmd.Parameters.Append CmdParamID   
43Cmd("@TransID") = SelectID   
44Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)   
45Cmd.Parameters.Append CmdParamName   
46Cmd.Execute   
47SelectName = Cmd("@TransName")   
48'---结束一个输入参数,一个输出参数的存储过程调用 
49
50Elseif TiggerType = 3 then 
51
52'---开始一个输入参数,一个输出参数,一个返回值的存储过程调用   
53Cmd.CommandText = "TransCompany3"   
54Set CmdParamReturn = Cmd.CreateParameter("Return_Value",3,4)   
55Cmd.Parameters.Append CmdParamReturn   
56Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)   
57Cmd.Parameters.Append CmdParamID   
58Cmd("@TransID") = SelectID   
59Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)   
60Cmd.Parameters.Append CmdParamName   
61  
62Cmd.Execute   
63SelectName = Cmd("@TransName")   
64SelectReturn = Cmd("Return_Value")   
65'---结束一个输入参数,一个输出参数,一个返回值的存储过程调用 
66
67End if 
68
69Conn.Close   
70Set Conn = Nothing   
71Set Cmd = Nothing   
72Set CmdParamID = Nothing   
73Set CmdParamname = Nothing   
74Set CmdParamReturn = Nothing 
Published At
Categories with Web编程
Tagged with
comments powered by Disqus