ASP calls SQLSERVER storage program
Source http://www.bmj2000.com/vb/techniccolumn/subDetail.asp?MainId=2&BoardId=asp&ItemId=%CA%FD%BE%DD%BF%E2%B2%D9%D7%F7&RecId=2216
Calling database stored procedures
<%
SetDataconn=Server.CreateObject("ADODB.Connection")
'Create a connection object
Dataconn.Open"DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft(R)DeveloperStudio;WSID=APP_SERVER;Regional=Yes"
SetcmdTemp=Server.CreateObject("ADODB.Command")
'Create a command object
Setrst=Server.CreateObject("ADODB.Recordset")
'Create a record set object
cmdTemp.CommandText="dbo.pd_test"'Stored procedure name
cmdTemp.CommandType=4
'Command category is 4, denoted as stored procedure
SetcmdTemp.ActiveConnection=Dataconn
SettmpParam=cmdTemp.CreateParameter("ReturnValue",3,4,4)
cmdTemp.Parameters.AppendtmpParam
SettmpParam=cmdTemp.CreateParameter("@BeginDate",135,1,16,riqi)
'Create an input parameter object
cmdTemp.Parameters.AppendtmpParam
rst.OpencmdTemp,,1,3
'Generate query results
%>
The stored procedure called here is pd_test, which is a standard method provided in ADO. However, there is a problem, that is, when there are more than two SELECT statements in the stored procedure, but it is logically impossible to execute at the same time, ADO will prompt you that there are too many SELECT statements in the stored procedure. The solution is to directly execute the stored procedure using the EXECUTE method of the CONNECTION object of ADO, as follows:
<%
SetDataconn=Server.CreateObject("ADODB.Connection")
'Create a connection object
Dataconn.Open"DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft(R)DeveloperStudio;WSID=APP_SERVER;Regional=Yes"
ss="EXECUTEdbo.pd_test"&"'"&riqi1&"'""
Setrs=dataconn.Execute(ss)
%>