Programming | Advanced 5. return value
The handling of function return values differs from the processing of stored procedure return values, which often leads to confusion. In a function, you often return a Boolean value to indicate whether the function is running successfully or not.
If somefunctionname () = True Then
' Function succeeded
However, when a stored procedure is invoked, the same method cannot be used, because the store runs with the Execute method and returns a recordset.
Set rsauthors = Cmdauthors.execute
If you do not get a return value, how do you determine if the stored procedure is executed correctly? When an error occurs, an error is reported, so that the error handling code provided in the previous chapter can be used to handle the error. But what about some non-fatal logic errors?
For example, consider adding a new employee to the employee table. You may not want to prevent two employees from having the same name, but want to indicate this. Then, you can use a return value to indicate whether an employee with the same name already exists. The stored procedure is as follows:
CREATE PROCEDURE Usp_addemployee
@Emp_ID Char (9),
@FName Varchar (20),
@Minit Char (1),
@LName Varchar (30),
@Job_ID SmallInt,
@Job_Lvl TinyInt,
@Pub_ID Char (4),
@Hire_Date Datetime
As
BEGIN
DECLARE @Exists Int--return value
--same name exists
IF EXISTS (SELECT *
From Employee
WHERE FName = @FName
and Minit = @MInit
and LName = @LName)
SELECT @Exists = 1
ELSE
SELECT @Exists = 0
INSERT into Employee (emp_id, fname, Minit, lname,
job_id, Job_lvl, pub_id, hire_date)
VALUES (@Emp_Id, @FName, @MInit, @LName, @Job_ID,
@Job_Lvl, @Pub_ID, @Hire_Date)
Return @Exists
End
The process first checks whether there is a staff member with the same name, and accordingly set the corresponding variable exists, if there is the same name, set to 1, otherwise 0. The employee is then added to the table, and the exists value is returned as the return value.
Note Although a value is returned, it is not declared as a parameter to the stored procedure.
The ASP code that invokes the procedure is as follows:
<!--#INCLUDE file= ". /include/connection.asp "-->
<%
Dim Cmdemployee
Dim Lngrecs
Dim lngadded
Set Cmdemployee = Server.CreateObject ("Adodb.command")
' Set the properties of the command
With Cmdemployee
. ActiveConnection = strconn
. CommandText = "Usp_addemployee"
. CommandType = adCmdStoredProc
' Create The parameters
' Notice ' The return value is the ' parameter '
. Parameters.Append. CreateParameter ("Return_value", adinteger, _
adParamReturnValue)
. Parameters.Append. CreateParameter ("@Emp_id", Adchar, adParamInput, 9)
. Parameters.Append. CreateParameter ("@fname", adVarWChar, adParamInput, 20)
. Parameters.Append. CreateParameter ("@minit", Adchar, adParamInput, 1)
. Parameters.Append. CreateParameter ("@lname", adVarWChar, adParamInput, 30)
. Parameters.Append. CreateParameter ("@job_id", adSmallInt, adParamInput)
. Parameters.Append. CreateParameter ("@job_lvl", Adunsignedtinyint, adParamInput)
. Parameters.Append. CreateParameter ("@pub_id", Adchar, adParamInput, 4)
. Parameters.Append. CreateParameter ("@hire_date", adDBTimeStamp, _
adParamInput, 8)
' Set the parameter values
. Parameters ("@Emp_id") = Request.Form ("Txtempid")
. Parameters ("@fname") = Request.Form ("txtFirstName")
. Parameters ("@minit") = Request.Form ("txtinitial")
. Parameters ("@lname") = Request.Form ("Txtlastname")
. Parameters ("@job_id") = Request.Form ("Lstjobs")
. Parameters ("@job_lvl") = Request.Form ("Txtjoblevel")
. Parameters ("@pub_id") = Request.Form ("Lstpublisher")
. Parameters ("@hire_date") = Request.Form ("Txthiredate")
' Run the stored procedure
. Execute Lngrecs, adExecuteNoRecords.
' Extract The return value
lngadded =. Parameters ("Return_value")
End With
Response.Write "New Employee Added.<p>"
If lngadded = 1 Then
Response.Write "An employee with the same name already exists."
End If
Set Cmdemployee = Nothing
%>
It is important to note that the return value should be created as the first parameter in the collection. The first parameters in the collection is always parameters, even if the return value does not appear as a parameter in the stored procedure.
[1] [2] [3] Next page
