Using stored procedures in asp
In order to improve the efficiency of the Asp program, sometimes it is necessary to use the storage technology using SQL Server in Asp. Here is a brief introduction.
Establishment of stored procedures
Here is a brief introduction to how to establish stored procedures in the enterprise manager of SQL Server:
(1) Open the enterprise manager
(2) Select the server group (sql server group), server, database (database) and the corresponding database, right-click the stored procdures item under the corresponding database, select new stored procedure in the pop-up menu, and in the stored procedure properties Enter a statement to create a stored procedure. Here is an example:
create procedure proctest @mycola char(10),@mycolb char(10),@mycolc text as
insert into chatdata (mycola,mycolb,mycolc) values(@mycola,@mycolb,@mycolc)
In the sql server's documentation, its syntax is:
create proc[edure] procedure_name [;number] [
{@parameter data_type} [varying] [= default] [output] ]
[,...n] [with { recompile | encryption
| recompile, encryption } ] [for replication] as
sql_statement [...n]
If you are not familiar with SQL syntax, you can use check syntax to check the syntax. In the above example, it means that the stored procedure is created with a stored procedure named mycola and has 3 parameters. The first parameter mycola data type is char and width 10; the second parameter data type is char and width 10. The data type of the 3 parameters is text, and the data type of the SQL server is used here.
After the stored procedure is established, the following is how to call the stored procedure in the asp program: In order to improve the efficiency of the asp program, sometimes it is necessary to use the storage technology using SQL Server in the asp. The following is a simple one, and the above The statement p.append cm.createparameter("@mycolc",201,1,250) with added parameters, the format is:
p.append cm.createparameter("parameter name", type, direction, size)
The meaning of the type of the parameter value is as follows:
Name value integer value function
addbtimestamp 135 Date time data type
addecial 14 decimal integer value
addouble 5 double precision decimal value
adrror 10 System Error Message
adguid 72 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 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
adunsignedsmalllint 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 character variable value
advarchar 200 character variable value
advarchar 202 Unencoded string variable value
adwchar 130 Unencoded string
The meaning of direction values is as follows:
Name value integer value function
adparaminput 1 Allow data to be entered into this parameter
adparamoutput 2 Allow data to be output to this parameter
adparaminputoutput 3 Allow data input and output to this parameter
adparamreturnvalue 4 Allows data to be returned from a subroutine to this parameter
For more detailed resources, please refer to the SQL Server documentation and IIS documentation resources.