How to use ASP stored procedures

1. Using the command object and parameter object to pass parameters this section mainly uses the Microsoft SQL Server7.0 database, first establishing a connection file adosql7.asp for backup, which will not be described in detail later.

% ''Adosql7. asp

Option explicit

Response. expires = 0

''Part 1: Establish a connection

Dim CNN, strcnn

Set CNN = server. Createobject ("ADODB. Connection ")

Strcnn = "provider = sqloledb; user id = sa; Password =; initial catalog = pubs; Data Source = icbczjp"

CNN. Open strcnn

%>

Note: Set Data source to the name of the machine where your database server is located.

In addition, in the past, Microsoft Access97 was used to conveniently View fields and data, while SQL Server databases were not used on database servers, when debugging ASP scripts on another machine, you need to install another tool to view the fields and data. Here we provide you with a tool: msqry32.exe (Microsoft query ), this file is installed with office97 and is generally located under the "Microsoft Office \ Office" directory.

For example, wuf70.asp:

% @ Language = "VBScript" %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf70.asp

Dim release test, prmtest, rstest

''Create command object

Set release test = server. Createobject ("ADODB. Command ")

'Recordset and command objects can all be connected to the connection object through the activeconnection attribute.

Using test. activeconnection = CNN

''SQL command-contains two parameters. Use? Indicates

Export test. commandtext = "Update jobs set job_desc =? Where job_id =? "

''Set command type to SQL statement

Export test. commandtype = ad1_text

The ''prepared attribute determines whether to compile the SQL command first and set it to true to accelerate the running.

Required test. Prepared = true

''Create a parameter object

Set prmtest = Route Test. createparameter ("job_desc", advarchar, adparaminput, 50, "network ")

''Append the data to the parameters dataset.

Using test. Parameters. append prmtest

Set prmtest = Route Test. createparameter ("job_id", adsmallint, adparaminput, "12 ")

Using test. Parameters. append prmtest

''Execute the modification-you do not need to return the result. Simply use test. Execute.

Revoke test. Execute

''Re-set the parameter run-you can modify another data

Using test. parameters ("job_id") = "1"

Using test. parameters ("job_desc") = "test"

Revoke test. Execute

''Re-set the parameter run

Using test ("job_id") = "14"

Principal test ("job_desc") = "finance"

Revoke test. Execute

Set rstest = CNN. Execute ("select job_id, job_desc from jobs ")

While not rstest. EOF

Response. Write rstest (0) & rstest (1 )&""

Rstest. movenext

Wend

CNN. Close: Set prmtest = nothing

Set broadcast test = nothing: Set CNN = nothing

%>

Analysis:

1. The createparameter method of the command object is used to create a parameter object for SQL commands or stored procedures. There are five parameters in total (the five parameters are optional ):

First parameter: name of the parameter object;

The second parameter: the data type of the parameter object. There are too many types. For more information, see ADO. Here, advarchar (string value) and adsmallint (two-byte signed integer );

Third parameter: parameter type. Can be: adparaminput (indicating the input parameter), adparamoutput (indicating the output parameter), adparamreturnvalue (indicating the return value), adparamunknown (indicating that the parameter type cannot be determined), adparaminputoutput (indicating the input/output parameters );

Fourth parameter: The data length of the parameter. It is best to specify the length of the corresponding field in the database to avoid errors during use, especially when the data type is varchar, this value is not required if it is an integer or a date type;

Fifth parameter: Initial Value of the parameter.

2. The parameter test. Parameters. append method adds a parameter object to the parameters dataset. In this example, you can also see how to use multiple parameters.

3. As shown in this example, you only need to reset the input parameters to modify other data, which is very convenient. This idea is also one of the most common methods in programming.

4. Reset the parameter. You can either use parameter test. parameters or omit the parameter test ("job_id ").

Ii. Use stored procedures in ASP

What is a stored procedure (stored procedure is located on the database server, a collection of SQL statements that can contain one or more SQL statements) and how to create a stored procedure is not part of this lecture, this lecture describes how to call a stored procedure in ASP.

The advantage of using stored procedures is that stored procedures are more efficient than running SQL commands in ASP scripts; improves overall performance and reduces network load (reduces interactions between network servers and data servers); and optimizes ASP.CodeAnd enhance code flexibility.

(1) Using input parameters in Stored Procedures

In this example, the stored procedure is "byroyalty" that comes with SQL Server7.0. An SQL statement in it is very simple. It is nothing more than a create procedure byroyalty, and there is an input parameter @ percentage:

Create procedure byroyalty @ percentage int

As

Select au_id from titleauthor

Where titleauthor. royaltyper = @ percentage

Serve by www.cidu.net

Example: wuf71.asp

% @ Language = VBScript %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf71.asp

Dim release test, prmtest, rstest

Set release test = server. Createobject ("ADODB. Command ")

Stored test. commandtext = "byroyalty" ''stored procedure name

''Set the command type to Stored Procedure

Export test. commandtype = adw.storedproc

''Create a parameter object

Set prmtest = server. Createobject ("ADODB. parameter ")

The 'Type property corresponds to the second parameter in wuf70.asp

Prmtest. type = adinteger ''4-byte signed integer

The 'direction' attribute corresponds to the third parameter in wuf70.asp.

Prmtest. Direction = adparaminput

'Value attribute corresponds to the fifth parameter in wuf70.asp

Prmtest. value = 30

Using test. Parameters. append prmtest

Set multicast test. activeconnection = CNN

'Use set rstest = keep test. Execute

Set rstest = Execution test. Execute

While not rstest. EOF

Response. Write rstest (0 )&""

Rstest. movenext

Wend

CNN. Close

Set rstest = nothing: Set prmtest = nothing

Set broadcast test = nothing: Set CNN = nothing

%>

You can specify the SQL command, stored procedure, or table name as the commandtext attribute.

In this example, creating a parameter object is slightly different from wuf70.asp. In fact, you can take a closer look at it. In this example, there are two unused properties: prmtest. name, prmtest. size, plus type, ction, and value, corresponds to the five parameters in wuf70.asp.

(2) Use output parameters

When obtaining a record from a database table or calculating a value, you need to use the stored procedure that returns the output parameters. For example, create a stored procedure outemploy in the SQL Server pubs database. The stored procedure requires two dates, and then outputs a maximum value.

Create procedure outemploy

(

@ Job_lvl tinyint output,

@ Hire_date1 datetime,

@ Hire_date2 datetime

)

As

Select @ job_lvl = max (job_lvl) from employee

Where hire_date >=@ hire_date1 and hire_date = @ hire_date2

There are multiple ways to create a stored procedure:

1. use Microsoft SQL Server's Enterprise Manager and open it in the left-side tree Directory: Console Root-Microsoft SQL servers-SQL Server group-icbczjp (Windows NT) -databases-pubs-stored procedure-New stored procedure: Enter the stored procedure and perform syntax check on it;

2. Use query Analyzer of Microsoft SQL Server to connect to the database server and select pubs database. Enter the preceding stored procedure and click execute query (or press F5 );

3. Use VB6.0 to open the menu "View"/"Data View window", right-click "Data Link"/"New Data Link ";

4. Use an ASP script to create a stored procedure, for example, wuf75.asp:

% @ Language = VBScript %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf75.asp

Dim strsql

''Note: & CHR (10) & CHR (13) is not recommended, mainly for good looks.

Strsql = "create procedure outemploy (@ job_lvl tinyint output," & CHR (10) & CHR (13 )&_

"@ Hire_date1 datetime, @ hire_date2 datetime) as" & CHR (10) & CHR (13 )&_

"Select @ job_lvl = max (job_lvl) from employee "&_

"Where hire_date >=@ hire_date1 and hire_date = @ hire_date2"

CNN. Execute strsql

Response. write "the stored procedure is successfully created"

CNN. Close: Set CNN = nothing

%>

After a stored procedure is created, you can also use the SQL statement "Drop procedure outemploy" to delete it.

Example: wuf72.asp-Send the required input parameters to the stored procedure and obtain the output result.

% @ Language = VBScript %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf72.asp

Dim release test, prmtest

Set release test = server. Createobject ("ADODB. Command ")

Using test. activeconnection = CNN

Stored test. commandtext = "outemploy" ''stored procedure name

Export test. commandtype = adw.storedproc

''Create a parameter object

Set prmtest = Route Test. createparameter ("job_lvl", adtinyint, adparamoutput)

Using test. Parameters. append prmtest

''Adtinyint-1 byte signed integer

''Addbdate-date value (yyyymmdd)

Set prmtest = partition test. createparameter ("hiredate1", addbdate, adparaminput ")

Using test. Parameters. append prmtest

Set prmtest = partition test. createparameter ("hiredate2", addbdate, adparaminput ")

Using test. Parameters. append prmtest

Revoke test. Execute

'The Three expressions below have the same meanings.

Response. Write into test ("job_lvl ")&""

Response. Write parameters test. parameters ("job_lvl ")&""

Response. Write parameters test. parameters ("job_lvl"). Value

CNN. Close

Set prmtest = nothing

Set broadcast test = nothing: Set CNN = nothing

%>

(3) Use response code Parameters

You can use the return statement to return different return codes from the stored procedure. For example, the following stored procedure obtains a record set. If an employee named Margaret is returned, 1 is returned. Otherwise, 0 is returned.

Create procedure returnemploy

As

Select emp_id, fname from employee

If exists (select fname from employee where fname = ''margaret '')

Return (1)

Else

Return (0)

Example: wuf73.asp

% @ Language = VBScript %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf73.asp

Dim release test, prmtest, rstest

Set release test = server. Createobject ("ADODB. Command ")

Using test. activeconnection = CNN

Export test. commandtext = "returnemploy" ''stored procedure name

Export test. commandtype = adw.storedproc

Set prmtest = release test. createparameter ("returnvalue", adinteger, adparamreturnvalue)

Using test. Parameters. append prmtest

Set rstest = Execution test. Execute ()

While not rstest. EOF

Response. Write rstest (0) & "] [" & rstest (1 )&""

Rstest. movenext

Wend

Rstest. Close: Set rstest = nothing

''Before returning release test (" returnvalue "), you must disable rstest. Otherwise, the result is incorrect.

If condition test ("returnvalue") = 1 then

Response. Write "has this employee"

Else

Response. Write "No employee"

End if

CNN. Close

Set prmtest = nothing

Set broadcast test = nothing: Set CNN = nothing

%>

Iii. How to deal with Big Data

Here, "Big Data" mainly refers to text and image fields, and the data cannot be correctly obtained using the method described above. You must first use size = rstest (0). actualsize to obtain the actual length of the field value, and then use rstest (0). getchunk (size) to obtain data. In actual use, because these fields are relatively large, in order to save and reasonably use server resources, we generally adopt the multipart read method. For example, wuf74.asp:

% @ Language = VBScript %>

! -- # Include file = "adosql7.asp" -->

! -- # Include file = "adovbs. Inc" -->

% ''Wuf74.asp

Dim strsql, rstest

'Pr _ info is a text field

Strsql = "select pr_info, pub_id from pub_info"

Set rstest = CNN. Execute (strsql)

Dim basicsize, beginsize, ltext

Do while not rstest. EOF

Response. Write rstest (1 )&""

'Read 1024 bytes each time

Basicsize = 1024

Beginsize = 0

While beginsize rstest (0). actualsize

Ltext = rstest (0). getchunk (basicsize)

Beginsize = beginsize + basicsize

'Output to the client segment by segment

Response. Write ltext

Wend

Response. Write ""

Rstest. movenext

Loop

CNN. Close

Set rstest = nothing: Set CNN = nothing

%>

In this example, a maximum of 1024 bytes can be read at a time, and multiple times can be read. Conversely, if you write big data to a database, the method is similar to above, but instead of using the getchunk method, you use the AppendChunk method:

Rstest (0). AppendChunk ltext

Note: The last tip is about SQL Server databases. If you have encountered such a problem: Chinese data in the database is garbled, please do not panic. You only need to download sqlsrv32.dll from my website to overwrite files with the same name under "C: \ WINDOWS \ SYSTEM. The source of the problem is the SQL Server Driver Program . A typical situation occurs in the second edition of Windows98 (the SQL Server Driver version is 3.70.06.23) or mdac2.5 is installed in Windows2000 (version 3.70.08.20 ).