ASP使用存储过程的总结

1.      什么是存储过程？

存储过程是SQL server所提供的Tranact-SQL语言所编写的程序。

2.      如何建立存储过程？

 

Create   Procedure  EmployeeID_Orders

@EmployeeID   as   int

as

select   *   from  orders

where  employeeID = @EmployeeID

-- =============================================================

if   exists ( select  name  from  sysObjects  where  name = ' insertOrder '   and  type = ' P ' )
DROP   procedure  insertOrder
GO
Create   procedure  insertOrder( @personId   as   int , @goodId   as   char ( 15 ))
AS
declare   @Pid   as   char ( 20 )
declare   @price   as   smallmoney
declare   @num   as   int
declare   @id   as   int
begin
   while   exists ( select   *   from  basket  where  personId = @personId )
     begin
           select   top   1   @Pid = Pid, @price = price, @num = num, @id = id  from  basket  where  personId = @personId
           update  Produce  set  PhaveNum = PhaveNum - @num ,PhavedSell = PhavedSell + @num   where  Pid = @Pid
         delete   from  basket  where  id = @id
       insert   into   [ order ] (goodId,Pid,price,num,personId)  values ( @goodId , @Pid , @price , @num , @personId )
     end  
end  

execute  insertOrder  10000 , ' 123132146 '

-- ===================================================================

 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