Sql
Copy Code code as follows:
Create PROCEDURE Proname//Creating a stored procedure by using the Create PROCEDURE proname for the stored procedure name
@Lname VARCHAR (30)//define incoming parameters
As
SET NOCOUNT on
BEGIN
SELECT * FROM tablename WHERE Lname like '% ' + @Lname + '% '///Use selece fuzzy query Lname value is similar to the value of the passed-in parameter Lname all rows
End
Go
The above is a stored procedure with one input parameter has been created, and the demerit sets for all queries are returned.
This stored procedure is now being manipulated using ASP.
Copy Code code as follows:
Here is a string to establish a connection with MSSQL server, as you all know, I will not speak.
strconnect = "Driver={sql Server}"; server= (local); uid=sa;password=;d Atabase=dataname "
Set conn = server. CreateObject ("Adodb.connection")
Conn. Open strconnect
Set cmd = Server.CreateObject ("Adodb.command")//Create a Command object
With cmd
Cmd. ActiveConnection = conn//conn for connection string
Cmd.commandtext = "Proname"//Here is the name of the stored procedure to use
Cmd.commandtype = 4//commandtype property indicates the type of request.
-1 indicates that the type of the CommandText parameter cannot be determined
1 indicates that CommandText is a generic command type
2 indicates that the CommandText parameter is a table name that exists
4 indicates that the CommandText parameter is the name of a stored procedure, so here CommandType = 4.
Cmd. Prepared = TRUE//requires that SQL commands be compiled first
Cmd. Parameters.Append cmd. CreateParameter ("@Lname", 3,1,4,lname)//Add parameter @lname, the Lname behind is the variable that you define in the ASP page.
Set rs = cmd. Execute//Set RS variable to get the returned query record set.
End With
Do but not Rs. EOF//Read the line in the RS recordset with Do Loop
RESPONSE. WRITE (RS (field name) "<br/>")
Rs. MOVENEXT//Move to the next one
Loop//End loop
Rs. Close//Off objects
