Following on from last week's introduction
to scripting stored procedures, we'll continue by looking at usp's that
return output parameters instead of recordsets. Output parameters are
useful where only one value can exist, such as a minimum or maximum
amount. The following script shows an example of a usp with an input
and output parameter. CREATE procedure usp_retrieve_latest_start_date @@stdate datetime OUTPUT,
@@grade varchar(12)
AS
SELECT @@stdate = MAX(start_date) FROM lut_employees
WHERE employee_grade = @@grade
This returns the output parameter, @@stdate , which represents the latest
date an employee joined the company for a given employee grade, @@grade.
This usp is called from the application as follows: With rsDemo
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.ActiveConnection = Nothing
End With
With cmmDemo
.Prepared = True
.ActiveConnection = cnConnection
.CommandType = adCmdStoredProc
.CommandText = "usp_retrieve_latest_start_date"
.Parameters.Append .CreateParameter("@@grade", _
adVarChar, adParamInput, 12)
.Parameters.Append .CreateParameter("@@stdate", _
adDBDate, adParamOutput, 11)
.Parameters("@@grade").Value = "Manager"
rst.Open cmmDemo
End With
|
