Learning to use stored procedures is one of the courses that ASP programmers must learn. All large databases support stored procedures. Today's article is a complete collection of the use of asp stored procedures shared by the editor of the editor of the new technology. Let's go to the following article to learn more!
1. Call stored procedures without parameters
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"
conn.Open strconn
set cmd.ActiveConnection=conn
cmd.CommandText="{call nono}"
'set rs=cmc.exe or cmd.execute
set rs=cmd.Execute()
%>
2. A stored procedure for input parameters
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"
conn.Open strconn
set cmd.ActiveConnection=conn
cmd.CommandText="{call oneinput(?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=100
cmd.Execute()
%>
3. An input parameter and an output parameter
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"
conn.Open strconn
set cmd.ActiveConnection=conn
cmd.CommandText = "{call oneinout(?,?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)
cmd.Execute()
bbb=cmd("@bbb")
%>
4. One input parameter, one output parameter, and one return value
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"
conn.Open strconn
set cmd.ActiveConnection=conn
cmd.CommandText="{?=call oneereturn(?,?)}"
cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)
cmd.Execute()
bbb=cmd("@bbb")
rrr=cmd("@return_value")
%>
How to call SQL stored procedure in ASP
www.lucky365.net 2002-2-7 Jixiang365
<%set connection1 = Server.CreateObject("ADODB.Connection")
connection1.open ... 'Join
set command1=Server.CreateObject("ADODB.command")
set command1.activeconnection=connection1
command1.commandtype=4
command1.commandtext="sp_1" 'SP name
command1.parameters(1)=... 'Parameters value
command1.parameters(2)=...
set recordset1=command1.execute()
%>
Tips for ASP calling stored procedures
1. The simplest one is as follows
Dim objConn
Set objConn = Server.CreateObject("ADOBD.Connection")
objConn.Open Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objConn.Execute "exec sp_AddHit"
No parameters, no return, no error handling, that's it
2. A call with parameters
objConn.Execute "exec sp_AddHit 'http://www.aspalliance.com', 1"
Please note that the split parameters are not returned to records.
3. Return to the record
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADOBD.Connection")
Set objRs = Server.CreateObject("ADOBD.Recordset")
objConn.Open Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objRs.Open objConn, "exec sp_ListArticles '1/15/2001'"
'Loop through recordset and display each article
4....
Dim objConn
Dim objCmd
'Instantiate objects
Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
conn.Open Application("ConnectionString")
With objCmd
.ActiveConnection = conn 'You can also just specify a connection string here
.CommandText = "sp_InsertArticle"
.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'Add Input Parameters
.Parameters.Append .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
.Parameters.Append .CreateParameter("@url", adVarChar, adParamInput, 255, url)
.Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 99, url)
.Parameters.Append .CreateParameter("@description", adLongVarChar, _
adParamInput, 2147483647, description)
'Add Output Parameters
.Parameters.Append .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
'Execute the function
'If not returning a recordset, use the adExecuteNoRecords parameter option
.Execute, , adExecuteNoRecords
link_id = .Parameters("@link_id")
End With
5. Code of stored procedure
Create PROCEDURE dbo.sp_InsertArticle
(
@columnist_id int,
@url varchar(255),
@title varchar(99),
@description text
@link_id int OUTPUT
)
AS
BEGIN
INSERT INTO dbo.t_link (columnist_id,url,title,description)
VALUES (@columnist_id,@url,@title,@description)
SELECT @link_id = @@IDENTITY
END
Several ways to call stored procedures with parameters
Recently, many friends have asked questions about calling stored procedures. Here are a brief introduction to several methods of ASP calling stored procedures with parameters.
1 This is also the easiest method, with two input parameters, no return value:
set connection = server.createobject("adodb.connection")
connection.open someDSN
Connection.Execute "procname varvalue1, varvalue2"
''Clear all objects into nothing, free up resources
connection.close
set connection = nothing
2 If you want to return the Recordset set:
set connection = server.createobject("adodb.connection")
connection.open someDSN
set rs = server.createobject("adodb.recordset")
rs.Open "Exec procname varvalue1, varvalue2",connection
''Clear all objects into nothing, free up resources
rs.close
connection.close
set rs = nothing
set connection = nothing
3 The above two methods cannot have a return value (except Recordset). If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the functions of C and VB return values; the other is to return multiple values,
The variable names that store these values need to be specified first in the call parameter.
This example will deal with multiple parameters, input parameters, output parameters, return recordsets and a direct return value (is it all enough?)
The stored procedure is as follows:
use pubs
GO
-- Create stored procedures
create procedure sp_PubsTest
-- Define three parameter variables, note the third one, the special mark is used for output
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT
AS
SELECT @intIDOut = @intID + 1
SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + ''%''
--Return a value directly
RETURN @intID + 2
The asp program that calls the stored procedure is as follows:
<%@ Language=VBScript %>
<%
Dim CmdSP
Dim adoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar
'These values are predefined constants in VB and can be called directly, but not predefined in VBScript
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
iVal = 5
oVal = 3
''Create a command object
set CmdSP = Server.CreateObject("ADODB.Command")
''Create a link
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
''Define the command object call name
CmdSP.CommandText = "sp_PubsTest"
''Set command call type as a stored procedure (adCmdSPStoredProc = 4)
CmdSP.CommandType = adCmdSPStoredProc
''Add parameters to the command object
''Define the stored procedure has a direct return value, and is an integer, the default value is 4
CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
''Define a character input parameter
CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")
''Define an integer input parameter
CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)
''Define an integer output parameter
CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)
''Run the stored procedure and get the return record set
Set adoRS = CmdSP.Execute
''Print out each record, the fields in it are virtual, you can ignore it
While Not adoRS.EOF
for each adoField in adoRS.Fields
Response.Write adoField.Name & "=" & adoField.Value & "
" & vbCRLF
Next
Response.Write "
"
adoRS.MoveNext
Wend
''Print two output values:
Response.Write "
@intIDOut = " & CmdSP.Parameters("@intIDOut").Value & "
"
Response.Write "
Return value = " & CmdSP.Parameters("RETURN_VALUE").Value & "
"
''Big cleaning
Set adoRS = nothing
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>
There are other ways, which are a little bit off, and I will talk about it later
This article has referenced many articles, and will not be listed here.
Using stored procedures in Asp
In order to improve the efficiency of Asp programs, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a brief introduction.
Establishment of stored procedures
Here is a brief introduction to how to establish stored procedures in the enterprise manager of Sql Server:
(1) Open Enterprise Manager
(2) Select the server group (SQL Server Group), server, database (Database) and the corresponding database, right-click the Stored Procdures item under the corresponding database, select New Stored Procedure in the pop-up menu, and enter the statement to create a stored procedure in Stored Procedures Properties. Here is an example:
CREATE PROCEDURE proctest @mycola Char(10),@mycolb Char(10),@mycolc text AS
Insert into chatdata (mycola,mycolb,mycolc) values(@mycola,@mycolb,@mycolc)
In the documentation of Sql Server, its syntax is:
CREATE PROC[EDURE] procedure_name [;number] [
{@parameter data_type} [VARYING] [= default] [OUTPUT] ]
[,...n] [WITH { RECOMPILE | ENCRYPTION
| RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS
sql_statement [...n]
If you are not familiar with Sql syntax, you can use Check Syntax to check the syntax. In the above example, it means that the stored procedure is created with a stored procedure named mycola and 3 parameters. The first parameter mycola data type is char and width 10; the second parameter data type is char and width 10, and the third parameter data type is text. Here is the data type of Sql Server.
After the stored procedure is established, the following is how to call the stored procedure in the Asp program: In order to improve the efficiency of the Asp program, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a simple way to add parameters in the above statement p.Append cm.CreateParameter("@mycolc", 201, 1, 250), the format is:
p.Append cm.CreateParameter("parameter name", type, direction, size)
The meaning of the type of the parameter value is as follows:
Name value integer value function
adDBTimeStamp 135 Date and Time Data Type
adDecimal 14 decimal integer value
adDouble 5 double precision decimal value
adError 10 System Error Message
AdGUID 72 Globally unique identifier
adDispath 9 COM/OLE automatic object (Automation Object)
adInteger 3 4-byte signed integer
adIUnknown 13 COM/OLE object
adLongVarBinary 205 Large 2-byte value
adLongVarChar 201 Large string value
adLongVarWChar 203 Large unencoded string
adNumeric 131 decimal integer value
adSingle 4 single-precision floating point decimal
adSmallInt 2 2-byte signed integer
adTinyInt 16 1 byte signed integer
adUnsignedBigInt 21 8-byte unsigned integer
adUnsignedInt 19 4-byte unsigned integer
adUnsignedSmallInt 18 2-byte unsigned integer
adUnsignedTinyInt 17 1 byte unsigned integer
adUserDefined 132 User-defined data type
adVariant 12 OLE object
adVarBinary 204 Double byte character variable value
adVarChar 200 character variable value
advarchar 202 Unencoded string variable value
adWchar 130 Unencoded string
The meaning of direction values is as follows:
Name value integer value function
adParamInput 1 Allow data to be entered into this parameter
adParamOutput 2 Allows data to be output to this parameter
adParamInputOutput 3 Allows data input and output to this parameter
adparamReturnValue 4 Allows data to be returned from a subroutine to this parameter
The above is a complete collection of the use of asp stored procedures collected by the editor of the Wrong New Technology Channel. I hope it will be of some help to your learning!