ASP invokes stored procedures with input and output parameters

The stored procedure return value is actually a special output parameter. In most cases, we use stored procedures that have both input and output parameters, such as the user name of an ID user in the User Information table, an input parameter----user ID, and an output parameter----user name. The stored procedure for realizing this function is as follows:<br><br> <table style= "width:475px; height:220px "bordercolor= #cccccc width=475 align=center bgcolor= #e3e3e3 border=1> <tr> <td>/*sp4*/ <br>create PROCEDURE dbo.getusername<br> @UserID int,<br> @UserName varchar <br>set nocount ON&LT;BR&GT;BEGIN&LT;BR&GT;IF @UserID is null return<br>select @UserName =username <br &GT;FROM dbo. [UserInfo] <br>where userid= @UserID <br>return<br>end<br>go</td></tr></ Table><br> the ASP code that invokes the stored procedure is as follows:<br><br> <table style= "WIDTH:507PX; height:242px "bordercolor= #cccccc width=507 align=center bgcolor= #e3e3e3 border=1> <tr> <td> ' * * Call stored procedures with input and output parameters **<br>dim Mycomm,userid,username<br>userid = 1<br>set Mycomm = Server.CreateObject ( "Adodb.command") <br>mycomm.activeconnection = Myconstr ' myconstr is a database connection string <br>mycomm.commandtext = "GetUserName" ' Specify stored procedure name <br> Mycomm.commandtype = 4 ' indicates that this is a stored procedure <br>mycomm.prepared = True ' requires the SQL command to be precompiled <br> ' declare parameters <br> MyComm.Parameters.append mycomm.createparameter ("@UserID", 3,1,4,userid) <br>mycomm.parameters.append Mycomm.createparameter ("@UserName", 200,2,40) <br>MyComm.Execute<br> ' Get the Ginseng <br>username = Mycomm (1 <br>set Mycomm = nothing</td></tr></table><br> in the above code, you can see that, unlike declaring a return value, you need 5 arguments to declare an input parameter. 4 parameters are required to declare an output parameter. When declaring an input parameter, 5 parameters are: Parameter name, parameter data type, parameter type, data length, parameter value. When declaring an output parameter, there is no last argument: parameter value. <br><br> It is important to note that when declaring a parameter, the order must be the same as the order defined in the stored procedure, and the data type and length of each parameter will be the same as defined in the stored procedure. <br><br> If the stored procedure has more than one parameter, the ASP code will appear cumbersome, and you can use the WITH command to simplify the code:<br><br> <table style= "WIDTH:473PX; height:243px "bordercolor= #cccccc width=473 align=center bgcolor= #e3e3e3 border=1> <tr> <td> ' * * Call stored procedures with input and output parameters (simplified code) **<br>dim Mycomm,userid,username<br>userid =1<br>set Mycomm = Server.CreateObject ("Adodb.command") <br>with mycomm<br>. ActiveConnection = Myconstr ' myconstr is a database connection string <br>. CommandText = "GetUserName" Specifies the stored procedure name <br>. CommandType = 4 ' indicates that this is a stored procedure <br>. Prepared = True ' requires the SQL command to be compiled in advance <br>. Parameters.Append. CreateParameter ("@UserID", 3,1,4,userid) <br>. Parameters.Append. CreateParameter ("@UserName", 200,2,40) <br>. Execute<br>end with<br>username = Mycomm (1) <br>set Mycomm = nothing</td></tr></ Table><br> if we were to get a username of 1 to 10, 10 users, would you like to create a 10 command object? No. If you need to call the same stored procedure multiple times, simply change the input parameters, you will get different output:<br><br> <table style= "WIDTH:489PX; height:372px "bordercolor= #cccccc width=489 align=center bgcolor= #e3e3e3 border=1> <tr> <td> ' * * Multiple calls to the same stored procedure **<br>dim mycomm,userid,username<br>username = "" <br>set Mycomm = Server.CreateObject (" Adodb.command ") <br>for UserID = 1 to 10<br> with mycomm<br>. Activeconnection = Myconstr ' myconstr is a database connection string <br>. CommandText = "GetUserName" Specifies the stored procedure name <br>. CommandType = 4 ' indicates that this is a stored procedure <br>. Prepared = True ' requires the SQL command to be precompiled <br> if UserID = 1 then<br>. Parameters.Append. CreateParameter ("@UserID", 3,1,4,userid) <br>. Parameters.Append. CreateParameter ("@UserName", 200,2,40) <br>. Execute<br> else<br> ' re-assign values (at which time the parameter values do not change and the argument does not need to be declared) <br>. Parameters ("@UserID") = userid<br>. Execute<br> End If<br> End with<br> UserName = UserName + mycomm (1) + "," ' Maybe you like to use array storage <br>next< Br>set Mycomm = nothing</td></tr></table><br> through the above code you can see that the same stored procedure is called repeatedly This method can greatly reduce the amount of code when there are multiple input and output parameters, and the value of only one input parameter changes every time you call it, simply by assigning a value to the input parameter that changes the value. <br> <P> <b>5. Stored procedures with return values, input parameters, output parameters, <br></b><br> previously said that when a stored procedure is invoked, the order of the arguments is declared in the same order as defined in the stored procedure. It is also important to note that if the stored procedure has both a return value and an input or output parameter, the return value is first declared. <br><br> to demonstrate the invocation method in this case, let's improve on the example above. Or get the user name of the user with ID 1, but there areCan the user not exist (the user has been deleted, and the UserID is a field that has grown from). Stored procedures return different values depending on whether the user is present or not. At this point, the stored procedure and ASP code are as follows:<br><br></p> <P> <table style= "WIDTH:479PX; height:654px "bordercolor= #cccccc width=479 align=center bgcolor= #e3e3e3 border=1> <tr> <td>/*sp5*/ <br>create PROCEDURE dbo.getusername<br>--in order to deepen the impression of "order", the following two parameters are defined in reverse order <br> @UserName varchar (40) output,<br> @UserID int<br>as<br>set nocount on<br>begin<br>if @UserID is null return <br>select @UserName =username <br>from dbo. [UserInfo] <br>where userid= @UserID <br>if @ @rowcount >0<br>return 1<br>else<br> Return 0<br>return<br>end<br>go<br><br> ' * * Invokes a stored procedure that has both a returned value, an input parameter, and an output parameter **<br>dim Mycomm,userid,username<br>userid = 1<br>set Mycomm = Server.CreateObject ("ADODB.") Command ") <br>with Mycomm<br> ActiveConnection = Myconstr ' myconstr is a database connection string <br>. CommandText = "GetUserName" ' specifies the stored procedure name <br>. CommAndtype = 4 ' indicates that this is a stored procedure <br>. Prepared = True ' requires the SQL command to be precompiled <br> ' return value first declared <br>. Parameters.Append. CreateParameter ("return", 2,4) <br> ' the declaration order of the following two parameters is also reversed <br>. Parameters.Append. CreateParameter ("@UserName", 200,2,40) <br> Parameters.Append. CreateParameter ("@UserID", 3,1,4,userid) <br> Execute<br>end with<br>if mycomm (0) = 1 then<br>username = Mycomm (1) <br>else<br> UserName = "This user does not exist" <br>end if<br>set Mycomm = nothing</td></tr></table></p> <P ><br> <b>6. Also returns stored procedures for parameters and recordsets <br></b><br> Sometimes we need stored procedures to return both parameters and recordsets, such as when using a stored procedure paging, returning both the recordset and the total amount of data. The following gives a paging process:<br><br></p> <P> <table style= "WIDTH:481PX; height:728px "bordercolor= #cccccc width=481 align=center bgcolor= #e3e3e3 border=1> <tr> <td>/*sp6*/ <br>create PROCEDURE dbo.getuserlist<br> @iPageCount int OUTPUT,--Total pages <br> @iPage int,--current page number <br > @iPagesize INT--The number of records per page <br>as<br>set nocount on<br>begin<br>--create temporary table <br>create table #t (ID int IDENTITY,--Self-increasing field <br>userid int,<br>username varchar ()) <br>--writes data to a temporary table <br>insert into # T <br>select userid,username from dbo. [Userinfo]<br>order by userid<br><br>--to get the total number of records <br>declare @iRecordCount Int<br>set @ iRecordCount = @ @rowcount <br><br>--Determine the total number of pages <br>if @iRecordCount% @iPageSize =0<br>set @ Ipagecount=ceiling (@iRecordCount/@iPageSize) <br>else<br>set @iPageCount =ceiling (@iRecordCount/@ Ipagesize) +1<br><br>--If the requested page number is greater than the total pages, the last page is displayed <br>if @iPage > @iPageCount <br>select @iPage = @ ipagecount<br><br>--determines the history of the current page <br>declare @iStart int--start record<br>declare @iEnd INT-- End Record<br>select @iStart = (@iPage-1) * @iPageSize <br>select @iEnd = @iStart + @iPageSize + 1<br>& lt;br>--take the current page record <br>select * from#t where id> @iStart and id< @iEnd <br><br>--delete the temporary table <br>drop table #t <br><br>--return the total number of records <br>return @iRecordCount <br>end<br>go</td></tr></table></P> <P> <br> in the above stored procedure, enter the current page number and the number of records per page, returning the current page's recordset, total pages and total number of records. To be more typical, the total number of records is returned in the form of a return value. The following is the ASP code that invokes the stored procedure (omitted from the specific paging operation):<br><br></p> <P> <table style= "WIDTH:403PX; Height:716px "bordercolor= #cccccc width=403 align=center bgcolor= #e3e3e3 border=1> <tr> <td> ' * * Call a paging stored procedure **<br>dim Pagenow,pagesize,pagecount,recordcount<br>dim Mycomm,myrst<br>pagenow = Request ("pn") <br> ' custom function to verify the natural number <br>if Checknar (pagenow) = False Then Pagenow = 1<br>pagesize = 20< Br>set Mycomm = Server.CreateObject ("Adodb.command") <br>with mycomm<br>. ActiveConnection = Myconstr ' myconstr is a database connection string <br>. CommandText = "Getuserlist" ' specifies the stored procedure name <br>. CommandType = 4 ' indicates that this is a stored procedure <br>. Prepared = True ' requires the SQL command to be precompiled &LT;br> ' return value (total record) <br>. Parameters.Append. CreateParameter ("return", 2,4) <br> ' Out parameters (total pages) <br>. Parameters.Append. CreateParameter ("@iPageCount", 3,2) <br> ' Enter parameter (current page number) <br>. Parameters.Append. CreateParameter ("@iPage", 3,1,4,pagenow) <br> ' Enter parameter (record number per page) <br>. Parameters.Append. CreateParameter ("@iPageSize", 3,1,4,pagesize) <br>set Myrst =. Execute<br>end with<br>if myrst.state = 0 Then ' not fetching data, Myrst close <br>recordcount = -1<br>else< Br>myrst.close ' NOTE: To get the value of a parameter, you first close the Recordset object <br>recordcount = Mycomm (0) <br>pagecount = Mycomm (1) <br>if CInt (Pagenow) >=cint (PageCount) then pagenow=pagecount<br>end-If<br>set = Mycomm Br> ' below display record <br>if RecordCount = 0 then<br>response.write "no record" <br>elseif RecordCount > 0 Then <br>myrst.open<br>do until myrst.eof<br>......<br>loop<br> ' show paging information <br> ... <br>else ' Recordcount=-1<br>response.write 'Number of errors "<br>end if</td></tr></table></p> <P><br> for the above code, There's only one point to note: When you return both a recordset and a parameter, to get a parameter, you close the recordset, and then open the recordset when you use it. </P>