ASP Stored Procedure Overview

1. Call a stored procedure 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 rsw.cmc.exe cmd.exe cute

Set rs = cmd. Execute ()

%>
2. Stored Procedure of an input 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 oneinput (?)} "
Cmd. Parameters. append cmd. createparameter ("@ AAA", adinteger, adparaminput)
CMD ("@ AAA") = 100

Cmd. Execute ()

%>
3. One input parameter and one 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 onereturn (?,?)} "

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 procedures in ASP
Www.lucky365.net 2002-2-7 auspicious 365

<% Set connection1 = server. Createobject ("ADODB. Connection ")
Connection1.open... 'connection
Set command1 = server. Createobject ("ADODB. Command ")
Set command1.activeconnection = connection1
Command1.commandtype = 4
Command1.commandtext = "sp_1" 'SP name
Command1.parameters (1) =... 'parameter value
Command1.parameters (2) =...
Set recordset11_command1.exe cute ()
%>

Skills for ASP to call stored procedures

1. The simplest 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 parameter, no response, no error handling.

2. A call with Parameters
Objconn. Execute "Exec sp_addhit 'HTTP: // www.aspalliance.com ', 1"
Note that the splitting parameter is not returned.

3.
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 '2017/123 '"
'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 = admo-storedproc '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

5. Stored Procedure Code
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 Methods for ASP to call stored procedures with Parameters
 
 
Author: China Forum network collection Source: http://www.51one.net join time:
 
Author: puppet

Several Methods for ASP to call stored procedures with Parameters

Recently, many users have asked questions about calling stored procedures. Here we will briefly introduce several methods for calling stored procedures with Parameters Using ASP.

1. This is also the simplest method. Two input parameters have no return value:
Set connection = server. Createobject ("ADODB. Connection ")
Connection. Open somedsn
Connection. Execute "procname varvalue1, varvalue2"

''Clear all objects as nothing and release resources
Connection. Close
Set connection = nothing

2. If you want to return the recordset:
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 as nothing and release resources
Rs. Close
Connection. Close
Set rs = nothing
Set connection = nothing

3 none of the above two methods can return values (except for recordset). To obtain the return value, use the command method.
First, there are two return values. One is to directly return a value in the stored procedure, just like the return values of C and VB functions; the other is to return multiple values.
The variable names for storing these values must be specified in the call parameters first.
In this example, we need to process multiple parameters, input parameters, output parameters, return record sets, and a direct return value (enough ?)
The stored procedure is as follows:

Use pubs
Go

-- Create a stored procedure
Create procedure sp_pubstest

-- Define three parameter variables. Note that the third parameter 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 + ''%''

-- Returns a value directly.
Return @ intid + 2

ASP that calls the Stored ProcedureProgramAs 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 they are not predefined in VBScript.
Adcmdspstoredproc = 4
Adparamreturnvalue = 4
Adparaminput = 1
Adparamoutput = 2
Adinteger = 3
Advarchar = 200

Ival = 5
Oval = 3

''Creates a command object.
Set cmdsp = server. Createobject ("ADODB. Command ")

''Create a connection
Cmdsp. activeconnection = "driver = {SQL Server}; server = (local); uid = sa; Pwd =; database = pubs"

''Defines the call name of the command object
Cmdsp. commandtext = "sp_pubstest"

''Set the command call type to stored procedure (admo-spstoredproc = 4)
Cmdsp. commandtype = adw.spstoredproc

''Add parameters to the command object
''Defines that the stored procedure has a direct return value and is an integer. The missing value is 4.
Cmdsp. Parameters. append cmdsp. createparameter ("return_value", adinteger, adparamreturnvalue, 4)
''Defines a struct input parameter
Cmdsp. Parameters. append cmdsp. createparameter ("@ au_lname", advarchar, adparaminput, 20, "M ")
''Defines an integer input parameter
Cmdsp. Parameters. append cmdsp. createparameter ("@ intid", adinteger, adparaminput, ival)
''Defines an integer output parameter
Cmdsp. Parameters. append cmdsp. createparameter ("@ intidout", adinteger, adparamoutput, oval)

''Run the stored procedure and obtain the returned record set
Set adors = cmdsp. Execute

''Print each record. The fields in the record are virtual.
While not adors. EOF

For each adofield in adors. Fields
Response. Write adofield. Name & "=" & adofield. Value & "<br>" & vbcrlf
Next
Response. Write "<br>"
Adors. movenext
Wend

''Print two output values:
Response. Write "<p> @ intidout =" & cmdsp. parameters ("@ intidout"). Value & "</P>"
Response. Write "<p> return value =" & cmdsp. parameters ("return_value"). Value & "</P>"

''Cleaning
Set adors = nothing
Set cmdsp. activeconnection = nothing
Set cmdsp = nothing
%>

In addition, there are other methods, which may be slightly biased and will be discussed later.
I have referenced many articlesArticle, Which is not listed here.

Use stored procedures in ASP

To improve the efficiency of ASP programs, you sometimes need to use SQL server storage technology in ASP. The following is a brief introduction.

Creation of Stored Procedures

Here we will only briefly introduce how to create a stored procedure in the Enterprise Manager of SQL Server:

(1) Open Enterprise Manager

(2) Select a server group (SQL Server group), a server, a database, and a similar database, and right-click the stored procdures item under the corresponding database, in the pop-up menu, select new stored procedure and enter the statement for creating the stored procedure in Stored Procedures properties. The following is an example:

Create procedure proctest @ mycola char (10), @ mycolb char (10), @ mycolc text

Insert into chatdata (mycola, mycolb, mycolc) values (@ mycola, @ mycolb, @ mycolc)

In SQL Server documents, its syntax is:

Create proc [edure] procedure_name [; number] [

{@ Parameter data_type} [varying] [= default] [Output]

[,... N] [With {recompile | Encryption

| Recompile, encryption}] [for replication]

SQL _statement [... n]

If you are not familiar with SQL syntax, you can use check syntax to check the syntax. In the preceding example, the stored procedure named mycola is created and contains three parameters. The data type of the first parameter mycola is Char and the width is 10; the data type of the 2nd parameters is Char, the width is 10, and the Data Type of the 3rd parameters is text. Here, the Data Type of SQL Server is used.

After a stored procedure is created, the following describes how to call the stored procedure in an ASP program: to improve the efficiency of the ASP program, sometimes you need to use SQL server storage technology in ASP. Here is a simple statement to add the parameter P. append cm. in createparameter ("@ mycolc", 250,), the format is:

P. append cm. createparameter ("parameter name", type, direction, size)

The parameter value types are as follows:

NAME value integer function

Addbtimestamp 135 Date and Time Data Type

Addecimal 14 decimal integer

Addouble 5 Double Precision small value

Aderror 10 system error message

Adguid 72 globally unique identifier (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

Adsingle 4 single precision Floating Point decimal point

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 variable value

Advarchar 200 character variable value

Advarchar 202 unencoded string variable value

Adwchar 130 unencoded string

The value of the direction is as follows:

NAME value integer function

Adparaminput 1 allows data input to 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

for more detailed resources, see SQL server documentation and IIS documentation resources.