Asp SQL stored procedure

1. asp calls the Stored Procedure

First, create a database in SQL called it. I won't talk about how to create it. This is simple and I will know it at a glance. Create a table in the database and write several data records as follows:

Open the query Analyzer:

Write the following statement and click the green button to execute it:

You can see in the stored procedure of the database that an additional Stored Procedure named upgetusername indicates that the stored procedure has been successfully created. If not, try to refresh it.

Use ASP to call it:
The conn file (connect to the database) is as follows:

<%
Set DB = server. Createobject ("ADODB. Connection ")
DB. Open ("driver = {SQL Server}; server = 192.168.18.254; uid = sa; Pwd =; database = it ;")
%>

192.168.18.254 is the IP address of the SQL Server server. uid PWD it is the user and password used to connect to the database and the database to be connected respectively.

Create an index. asp file with the following content:

<! -- # Include file = "conn. asp" -->
<%
Set rs = server. Createobject ("ADODB. recordset ")
SQL = "Exec upgetusername"
Rs. Open SQL, DB, 3, 2

Response. Write Rs. recordcount & "<br>"
While not Rs. EOF
Response. Write RS ("uname") & "<br>"
Rs. movenext
Wend
Response. End
%>

If you execute this page in a browser, it will be shown as follows:

Congratulations!
The above is a stored procedure call without parameters, but we will certainly have parameters in use. Here we will introduce a parameter.
In the above example, we changed the original stored procedure to the following:

Create proc upgetusername
@ Intuserid nvarchar (50 ),
@ Intuserpass nvarchar (50)
As
Begin
Select uname from users where uid = @ intuserid and pass = @ intuserpass

End
Go

You can delete the original stored procedure and write it in the query analyzer for execution. You can also change it directly in the original stored procedure.
@ Intuserid nvarchar (50 ),
@ Intuserpass nvarchar (50)
It is a parameter to be transmitted. @ is required. Because there are two parameters, they are separated by commas (,).
The index file is changed to the following:

<! -- # Include file = "conn. asp" -->
<%
Set rs = server. Createobject ("ADODB. recordset ")
SQL = "Exec upgetusername 'snake ', 'snake '"
Rs. Open SQL, DB, 3, 2

Response. Write Rs. recordcount & "<br>"
While not Rs. EOF
Response. Write RS ("uname") & "<br>"
Rs. movenext
Wend
Response. End
%>

Note: SQL = "Exec upgetusername 'snake ', 'snake '"
Two snakes do not mean one. One is uid, the other is pass, and the stored procedure returns uid = "snake" and pass = "snake" records.
There is only one such record in the database, so it will display:

ASP Stored Procedure usage
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

<% 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

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

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 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 ")

''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 are slightly biased. Later I will talk about it more slowly. I have referenced many articles in this article, which are 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] [

{@ ParameterData_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.