After many friends learn ASP, they only know a few basic commands such as SELECT, UPDATE, and DELETE. This greatly reduces the speed of the system when reading or storing data. sql
Copy the code code as follows:
CREATE PROCEDURE Proname //Use CREATE PROCEDURE to create a stored procedure Proname is the name of the stored procedure
@Lname VARCHAR(30) //Define the parameters passed in
AS
SET NOCOUNT ON
BEGIN
SELECT * FROM TableName WHERE Lname like '%'+@Lname+'%' //Use SELECE to fuzzy query all rows whose Lname value is similar to the value of the incoming parameter Lname
END
GO
The above is that a stored procedure with one input parameter has been created and returns the set of demerits for all queries.
Let's start using ASP to operate this stored procedure.
Copy the code code as follows:
//The following is the string to establish a connection with MSSQL SERVER. Everyone knows it, so I won’t go into it.
strConnect = Driver={SQL Server};Server=(local);uid=sa;password=;database=dataname
Set conn = server.CreateObject (Adodb.Connection)
conn.Open strConnect
SET cmd = Server.CreateObject(ADODB.Command) //Create a COMMAND command object
with cmd
cmd.ActiveConnection = conn //conn is the connection string
cmd.CommandText = Proname //Here is the name of the stored procedure to be used
cmd.CommandType = 4 //The CommandType attribute indicates the type of request.
//-1 indicates that the type of the CommandText parameter cannot be determined
//1 indicates that CommandText is a general command type
//2 indicates that the CommandText parameter is an existing table name
//4 indicates that the CommandText parameter is the name of a stored procedure, so CommandType = 4 here.
cmd.Prepared = true //Requires SQL commands to be compiled first
cmd.Parameters.append cmd.CreateParameter(@Lname,3,1,4,Lname) //Add parameter @Lname, the following Lname is the variable defined by yourself in the ASP page.
Set rs = cmd.Execute //Set the rs variable to obtain the returned query record set.
end with
DO WHILE NOT rs.EOF //Use DO loop to read the lines in the rs record set
RESPONSE.WRITE(rs(field name)<BR />)
rs.MOVENEXT //Move to the next item
LOOP //end loop
rs.CLOSE //Close the object