Summary of using Command object to invoke MSSQL stored procedure properties in ASP

Command|command objects |sql| stored procedures in many articles, the introduction of the MSSQL stored procedure (Stored Procedure) is invoked in ASP. There are two ways to take advantage of a Recordset object, or to use a command object directly.

The Recordset object is fairly simple, familiar to the ASP can be hands-on, here is a simple example:
Set up stored procedures in Mssql sp_userlist:
CREATE PROCEDURE Sp_userlist
@Uid int
As
SELECT * from Users where Uid = @Uid
Return
Go

To execute a stored procedure in SQL Server:
DECLARE @Uid int
Execute Sp_userlist 1

Call using a Recordset object:
Uid = 1
Set Rs=server.createobject ("Adodb.recordset")
sql = "exec sp_userlist" &Uid& ""
Rs.Open sql,conn,1,1

Or:
Uid = 1 ' input parameter for stored procedure, set this value directly to simplify the program
sql = "Sp_userlist" &Uid& ""
Set rs = conn.execute (SQL) or Set rs = Conn.execute ("exec sp_userlist" &Uid& ")

If you use the command object to invoke a stored procedure instead, you must first load the Adovbs.inc file (file Adovbs.inc can be found under C:\Program Files\Common Files\System\ado):
<!--must load the Adovbs.inc file, otherwise there will be an error-->
<!--#include file= "Adovbs.inc"-->
'-----Set up the Connection object----------
Set Conn = Server.CreateObject ("Adodb.connection")
Conn.Open "Driver={sql server};server=localhost;uid=sa;pwd=;d atabase=mydbase;"

Uid = 1
'-----Establish the Command object-----------
Set Comm = Server.CreateObject ("Adodb.command")
Comm.activeconnection = Conn
Comm.commandtext = "sp_userlist" stored procedure name, specifying a stored procedure to execute
Comm.commandtype = 4 ' adCmdStoredProc = 4, which is stored Procedure

'-----Prepare the parameters for Stored Procedure-------
Comm.Parameters.Append comm.createparameter ("@Uid", adinteger,adparaminput,4,uid) ' command format set parameter = command. CreateParameter (Name, Type, Direction, Size, Value)

'-----or use the following methods to define parameters for Stored Procedure: First create the input parameter object, and then add the parameter to the parameter collection-------
' Set Commfirstparam = comm.createparameter (' @Uid ', Adinteger,adparaminput,4,uid) ' Creates an input parameter object
' Comm.Parameters.Append commfirstparam ' adds parameters to the parameter set

'-----Execute the stored procedure----------------------
Comm.execute

'-----output parameter results below----------------------
Response.Write Comm.parameters ("@Uid")

Finally closes the Command object: Set Comm = Nothing. If you are using a method to create an input parameter object in Stored Procedure parameters, you must also close the object at the end: Set Commfirstparam = Nothing

Again, when using the command to tune the stored procedure, you must use include to load the Adovbs.inc file beforehand, otherwise it will be wrong!!

　　 The following is a description of each property used in the Command object.

1, ActiveConnection property: Indicates the Connection object to which the specified Command or Recordset object currently belongs.

2. CommandText property: Contains text to be sent according to the provider. Sets or returns a string value that contains a provider command, such as an SOL statement, a table name, or a stored procedure call. The default value is "" (0 length string).

3, CommandType property: Indicates the type of Command object.
Set and return values:
adCmdText: Calculates the CommandText as a textual definition of a command or stored procedure call.
adCmdTable: Computes CommandText as the name of the table returned by the internally generated SQL query as its columns.
adCmdTableDirect: Calculates the CommandText as the name of the table returned by all of its columns.
adCmdStoredProc: Calculates the CommandText as the stored procedure name.
adCmdUnknown: Default value. The command type in the CommandText property is unknown.
adCmdFile: Calculates the CommandText as a persistent Recordset file name.
adExecuteNoRecords: Indicates that CommandText is a command or stored procedure that does not return rows (for example, a command that inserts data). If any rows are retrieved, the rows are discarded and are not returned. It is always combined with adCmdText or adCmdStoredProc.

4. CreateParameter property: Creates a new Parameter object with the specified property. Returns the Parameter object.
Syntax: Set parameter = command. CreateParameter (Name, Type, Direction, Size, Value)
Parameter description:
Name: Optional, string that represents the Parameter object name.
Type: optional, long integer value specifying the Parameter object data type. See Type properties for valid settings.
Direction: Optional, long integer value specifying the Parameter object type. For valid settings, see Direction property.
Size: Optional, Long value that specifies the maximum length of the parameter value, in characters or bytes.
Value: Optional, Variant type that specifies the value of the Parameter object.

When the command object is used in the previous example, we can also implement this in the "Preparing Stored Procedure Parameters":
Comm.Parameters.Append comm.createparameter ("Uid", adinteger,adparaminput,4)
Comm ("Uid") =1

So when we use the stored procedure (Stored Procedure) parameter, we can use three methods to implement it.

The various properties that are used by stored procedures in ASP are briefly introduced here. In addition to passing input parameters, the return code and output parameter can be used to return values during stored procedure usage, and as a result of space, the author is no longer an example and is interested in referencing the ADO section and the Help documentation for SQL Server 2000 in the MSDN documentation for VB6.0.

　　 attached: Writing format for stored procedures
CREATE PROCEDURE [owner.] stored procedure name [; program number]
[Parameter # #,... Parameter #1024)]
[With
{RECOMPILE | Encryption | RECOMPILE, encryption}
]
[For REPLICATION]
As
Program Line

　　 Type Property
adDBTimeStamp 135 Date-time data type
Addecimal 140 Feed integer value
addouble 5 Double Precision Decimal value
Aderror 10 System error message
ADGUID 72 domain-wide unique identifier (globally unique identifier)
Addispath 9 Com/ole Automatic objects (Automation object)
Adinteger 3 4-byte signed integer
adIUnknown Com/ole Object
Adlongvarbinary 205 Large 2-byte value
adLongVarChar 201 Large String value
adLongVarWChar 203 Large encoded string
Adnumeric 1310 Feed 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 types
Advariant OLE Object
Advarbinary 204 double-byte Character Variable value
adVarChar 200 Character Variable value
adVarChar 202 not encoded string variable value
Adwchar 130 not encoded string

　　 Direction Property
Name Value integer value function
adParamInput 1 allows data to be entered into this parameter
adParamOutput 2 allows data to be exported to this parameter
adParamInputOutput 3 allows data input and output to this parameter
adParamReturnValue 4 allows you to return data from one subroutine to this parameter