About   -   Contact   -   Purchase   -   Search   -   What's New

Stored Procedure Creation(2)
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) 
   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

Top of Page

Legal Notice

Ken Howe 2011