ASP calls Stored Procedure 2

5. Stored Procedures with return values, input parameters, and output parameters
As mentioned above, when calling a stored procedure, the order of declared parameters must be the same as that defined in the stored procedure. Note: If a stored procedure has both return values and input and output parameters, the return values must be declared first.

To demonstrate the call method in this case, we can improve the above example. The username of the user whose ID is 1 is obtained, but the user may not exist (the user has been deleted, and the userid is a self-increasing field ). The stored procedure returns different values based on whether the user exists. In this case, the stored procedure and ASP Code As follows:

/* SP5 */
Create procedure DBO. GetUserName

-- To enhance the impression of "sequence", the following two parameters are defined in reverse order.
@ 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

'** Call a stored procedure with both return values, input parameters, and output parameters **

Dim mycomm, userid, username

Userid = 1

Set mycomm = server. Createobject ("ADODB. Command ")

With mycomm

. Activeconnection = myconstr 'Myconstr is the database connection string
. Commandtext = "GetUserName" 'Specifies the stored procedure name

. Commandtype = 4 'Indicates that this is a stored procedure

. Prepared = true 'Requires that the SQL command be compiled first
'Return value must be declared first
. Parameters. append. createparameter ("return", 2, 4)

'The Declaration Order of the following two parameters is also reversed.
. Parameters. append. createparameter ("@ username", 40)

. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)

. Execute

End

If mycomm (0) = 1 then

Username = mycomm (1)

Else

Username = "this user does not exist"

End if

Set mycomm = nothing

6. Stored Procedures that return both parameters and Record Sets

Sometimes, we need a stored procedure to return both parameters and record sets. For example, when using Stored Procedure paging, we need to return parameters such as the record set and total data. The following is a stored procedure for paging:

/* SP6 */
Create procedure DBO. getuserlist

@ Ipagecount int output, -- Total number of pages
@ Ipage int, -- Current page number

@ Ipagesize int -- Number of records per page
As

Set nocount on

Begin

-- Create a temporary table

Create Table # T (ID int identity, -- Auto-increment Field
Userid int,

Username varchar (40 ))

-- Write data to the temporary table
Insert into # T

Select userid, username from DBO. [userinfo]

Order by userid



-- Retrieve the total number of records

Declare @ irecordcount int

Set @ irecordcount = @ rowcount

-- determine the total number of pages

If @ irecordcount % @ ipagesize = 0

set @ ipagecount = ceiling (@ irecordcount/@ ipagesize)

else

set @ ipagecount = ceiling (@ irecordcount/@ ipagesize) + 1


-- if the requested page number is greater than the total page number, the last page

If @ ipage> @ ipagecount

select @ ipage = @ ipagecount

-- Determine the start and end records of the current page
Declare @ istart int -- start record

Declare @ iend int -- end record

Select @ istart = (@ ipage-1) * @ ipagesize

Select @ iend = @ istart + @ ipagesize + 1

-- Retrieve the current page record

Select * from # t where ID> @ istart and ID <@ iend

-- Delete a temporary table

Drop table # T

-- Total number of returned records

Return @ irecordcount

End

Go

In the preceding stored procedure, enter the current page number and the number of records on each page to return the record set, total page number, and total number of records on the current page. To be more typical, the total number of records is returned as a return value. The following is the ASP code that calls the stored procedure (the specific paging operation is omitted ):