1. 什么是存储过程?
存储过程是SQL server所提供的Tranact-SQL语言所编写的程序。
2. 如何建立存储过程?
Create Procedure EmployeeID_Orders
@EmployeeID as int
as
select * from orders
where employeeID=@EmployeeID
3. ASP中执行存储过程:
A. 编写sql语句:“execute 存储过程名 参数”,再通过connection.execute或recordset.open执行
strSql="execute employeeID_Orders 1"
Set objRstOrders=objCnnNorthwind.Execute(strSql)
B. 通command对象执行类型为acCmdStoredProc的命令
‘建立Command对象
Set objCmdNorthwind=Server.CreateObject("ADODB.Command")
‘设定命令的文本
objCmdNorthwind.CommandText="EmployeeID_Orders"
‘设定命令的类型
objCmdNorthwind.CommandType=adCmdStoredProc
‘设定命令对象使用的连接对象
Set objCmdNorthwind.ActiveConnection=objCnnNorthwind
‘建立参数对象
Set objParam=objCmdNorthwind.CreateParameter("@EmployeeID",adInteger,adParamInput)
‘把参数对象添加到命令对象的参数集中
objCmdNorthwind.Parameters.Append objParam
‘设定参数的值
objParam.Value=2
‘执行命令对象
Set objRstOrders=objCmdNorthwind.Execute()
‘销毁命令对象
Set objCmdNorthwind=Nothing
存储过程是SQL server所提供的Tranact-SQL语言所编写的程序。
2. 如何建立存储过程?
Create Procedure EmployeeID_Orders
@EmployeeID as int
as
select * from orders
where employeeID=@EmployeeID
3. ASP中执行存储过程:
A. 编写sql语句:“execute 存储过程名 参数”,再通过connection.execute或recordset.open执行
strSql="execute employeeID_Orders 1"
Set objRstOrders=objCnnNorthwind.Execute(strSql)
B. 通command对象执行类型为acCmdStoredProc的命令
‘建立Command对象
Set objCmdNorthwind=Server.CreateObject("ADODB.Command")
‘设定命令的文本
objCmdNorthwind.CommandText="EmployeeID_Orders"
‘设定命令的类型
objCmdNorthwind.CommandType=adCmdStoredProc
‘设定命令对象使用的连接对象
Set objCmdNorthwind.ActiveConnection=objCnnNorthwind
‘建立参数对象
Set objParam=objCmdNorthwind.CreateParameter("@EmployeeID",adInteger,adParamInput)
‘把参数对象添加到命令对象的参数集中
objCmdNorthwind.Parameters.Append objParam
‘设定参数的值
objParam.Value=2
‘执行命令对象
Set objRstOrders=objCmdNorthwind.Execute()
‘销毁命令对象
Set objCmdNorthwind=Nothing