Detailed description of SQL server Stored Procedures called by ASP-attached instances-

The ado document creates a new Parameter object using the specified property.
Syntax

Set parameter = command. CreateParameter (Name, Type, Direction, Size, Value)

Return Value

Returns the Parameter object.

Parameters

Name (optional) is a string that represents the Name of a Parameter object.

Type (optional) long integer value, which specifies the Data Type of the Parameter object. For effective settings, see Type attribute.

(Optional) Direction, which is a long integer value and specifies the Parameter object type. For effective settings, see the Direction attribute.

Size (optional) long integer value. It specifies the maximum length (in characters or bytes) of the parameter value ).

Value (optional) indicates the Value of the Parameter object.

Description

Use the CreateParameter method to create a new Parameter object with the specified name, type, direction, size, and value. All values transmitted in parameters are written to the corresponding Parameter attribute.

This method cannot automatically append a Parameter object to the Parameter set of the Command object, so that you can set additional attributes. If you append a Parameter object to a set, ADO will apply the value of this additional property.

If you specify a variable-length data Type in the Type Parameter, you must transmit the Size Parameter or set the Size attribute ring of the Parameter object before appending it to the Parameters set? Br>
========================================================== ==========================================================
The parameter value type has the following meanings:

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.
========================================================== ==========================================================
Stored Procedure introduction:

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)

========================================================== ==========================================================
Asp call method:
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 that multiple values can be returned. 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 ?)
(See the example I wrote that has been debugged.) instance:

'================================================ ====================
'Function: Call the stored procedure to delete a topic
'Back: NO
'By king
'================================================ ====================
Function Fun_delete_column (DelScope, Site_id, column_id)
'Create proc pro_delte_column
'@ Del_scope varchar (100 ),
'@ Site_ID int,
'@ COLUMN_ID int,
'@ Back_message varchar (100) output
'Establish a database connection
Set Comm = Server. CreateObject ("ADODB. Command ")
Comm. ActiveConnection = conn
'Establish a stored procedure connection with the comm object. 4 indicates the connection type as the stored procedure.
Comm. CommandText = "pro_delte_column"
Comm. CommandType = 4
'Use p1 as the name to create the parameter method of the comm object. Append the first parameter fullname to the p1 set.
Set p1 = Comm. CreateParameter ("@ Del_scope", 200,1, 100, DelScope)
Comm. Parameters. Append p1
Set p1 = Comm. CreateParameter ("@ Site_ID", 3, 1, Site_ID)
Comm. Parameters. Append p1
Set p1 = Comm. CreateParameter ("@ COLUMN_ID", 3, 1, column_id)
Comm. Parameters. Append p1
'Use p1 as the name to create the parameter method of the comm object. Append the third check parameter to the p1 set.
Set p1 = Comm. CreateParameter ("@ back_message", 100)
Comm. Parameters. Append p1
'Run the Stored Procedure
Comm. Execute
'Submit the result for processing
If comm ("@ back_message") = "Success" then
Response. write "welcome to the system! "
Else
'Output value:
Response. Write"

@ Back_message = "& comm. Parameters (" @ back_message "). Value &"

"
End if
'Release the connection
Set Comm = nothing