The ASP code that calls the stored procedure is as follows:
Copy the codeThe code is as follows:
'**Calling stored procedure with input and output parameters**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = ("")
= MyConStr 'MyConStr is the database connection string
= "getUserName" 'Specify the stored procedure name
= 4 ' indicates that this is a stored procedure
= true 'Require SQL command to be compiled first
'Declare parameters
("@UserID",3,1,4,UserID)
("@UserName",200,2,40)
'Get out the stake
UserName = MyComm(1)
Set MyComm = Nothing
In the above code, we can see that unlike declaring the return value, 5 parameters are required when declaring the input parameters, and 4 parameters are required when declaring the output parameters. When declaring input parameters, the 5 parameters are: parameter name, parameter data type, parameter type, data length, and parameter value. When declaring the output parameter, there is no last parameter: parameter value.
It should be noted that when declaring parameters, the order must be the same as the order defined in the stored procedure, and the data type and length of each parameter must also be the same as defined in the stored procedure.
If the stored procedure has multiple parameters, the ASP code will appear cumbersome, and you can use the with command to simplify the code:
Copy the codeThe code is as follows:
'**Calling stored procedures with input and output parameters (simplified code)**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = ("")
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string
.CommandText = "getUserName" 'Specify stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Require SQL command to be compiled first
. .CreateParameter("@UserID",3,1,4,UserID)
. .CreateParameter("@UserName",200,2,40)
.Execute
end with
UserName = MyComm(1)
Set MyComm = Nothing
If we want to obtain the username of the 1 to 10 users, should we create a Command object 10 times? No. If you need to call the same stored procedure multiple times, just change the input parameters and you will get different outputs:
Copy the codeThe code is as follows:
'**Call the same stored procedure multiple times**
DIM MyComm,UserID,UserName
UserName = ""
Set MyComm = ("")
for UserID = 1 to 10
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string
.CommandText = "getUserName" 'Specify stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Require SQL command to be compiled first
if UserID = 1 then
. .CreateParameter("@UserID",3,1,4,UserID)
. .CreateParameter("@UserName",200,2,40)
.Execute
else
'Add value to the incoming parameter (the incoming parameter and outgoing parameter whose parameter value does not change at this time does not need to be redeclared)
.Parameters("@UserID") = UserID
.Execute
end if
end with
UserName = UserName + MyComm(1) + "," 'Maybe you like to use array storage
next
Set MyComm = Nothing
From the above code, we can see that when the same stored procedure is repeatedly called, you only need to reassign the value to the input parameters whose value has changed. This method can greatly reduce the amount of code when there are multiple input and output parameters and only one input parameter's value changes every time the call is called.
5. Stored procedures with return values, input parameters, and output parameters at the same time
As mentioned earlier, when calling stored procedures, the order of declared parameters must be the same as the order defined in the stored procedures. Another thing to note is that if the stored procedure has both return value and input and output parameters, the return value must be declared first.
To demonstrate the call method in this case, we will improve the above example. It is still obtained the username of the user with ID 1, but it is possible that the user does not exist (the user has been deleted, and userid is a self-growing field). Stored procedures return different values depending on whether the user exists or not. At this time, the stored procedure and ASP code are as follows:
Copy the codeThe code is as follows:
/*SP5*/
CREATE PROCEDURE
--In order to deepen the impression of "order", reverse the definition order of the following two parameters.
@UserName varchar(40) output,
@UserID int
as
set nocount on
begin
if @UserID is null return
select @UserName=username
from dbo.[userinfo]
where userid=@UserID
if rowcount> 0
return 1
else
return 0
return
end
go
Previous page12345Next pageRead the full text