<< Click to Display Table of Contents >> Navigation:  Examples > Command > Command  3: Calling Stored Procedures 2

Below is an example how you can query a stored procedure for its parameters:

Please note that to read the Return-value and Output parameters you have to do two things:

1. Make the cursorlocation AdUseClient

OR

2. Close the resultset first

 

FUNCTION Start

 LOCAL oRs AS AdoRecordSet

 LOCAL oConn AS AdoConnection

 LOCAL oCmd        AS AdoCOmmand

 LOCAL oPars        AS AdoParameters

 LOCAL oPar        AS AdoParameter

 LOCAL i        AS DWORD

 oConn := OpenConnection()

 CreateProc2(oConn)

 

 oCmd := AdoCommand{}

 oCmd:ActiveConnection        := oConn

 oCmd:CommandText        := "sp_orderTotal"

 oCmd:CommandType        := AdCmdStoredProc

 oPars := oCmd:Parameters_

 oPars:Refresh()

 ? "Query stored procedure for parameters"

 ? "Parameters"

 FOR i := 1 TO oPars:Count

 oPar := oPars:[Item,i]

 ? i, PadR(oPar:Name,12) ,;

 PadR(AdoEnum2Str(oPar:Direction, ;        AdoParameterDirectionEnum(),FALSE),20), ;

 AdoEnum2Str(oPar:TYPE, AdoDataTypeEnum(), FALSE)

 NEXT

 wait

 oPars:[Item, "@dStart"]:Value        := 1996.07.01

 oPars:[Item, "@dEnd"]:Value        := 1996.07.31

 oRs := oCmd:Execute(NIL,NIL,NIL)

 ?

 ? "List of results"

 ?

 DO WHILE ! oRs:EOF

 FOR i := 1 TO oRs:Fields:Count

 ?? PadL(oRs:FIELDGET(i),12)

 NEXT

 ?

 oRs:MoveNext()

 ENDDO

 wait

 oRs:Close()

 ? "Value OF RETURN and Output PARAMETER"

 ? oPars:[Item, "@Return_Value"]:Value

 ? oPars:[Item, "@mtotal"]:Value

 wait

 

 

FUNCTION CreateProc2(oConn AS AdoConnection) AS VOID

LOCAL cbErr AS CODEBLOCK

LOCAL cProc        AS STRING

 cbErr := ErrorBlock({|e|_Break(e)})

BEGIN SEQUENCE

       oConn:Execute("Drop Procedure sp_orderTotal", NIL,NIL)

END

 ErrorBlock(cbErr)

 cProc := "create procedure sp_OrderTotal" + CRLF + ;

       " (@dStart DateTime , @dEnd DateTime, "+ CRLF + ;

       " @mTotal Money OUTPUT )" +CRLF+ ;

       " as " + CRLF + ;

       " BEGIN " + CRLF + ;

 " select @mTotal = Sum(od.UnitPrice * Od.Quantity * (1 - Od.Discount)) " + CRLF+;

 " From Orders O, [Order Details] OD " + CRLF+;

 " where O.orderId = OD.OrderId " + CRLF+;

 " and O.Orderdate between  @dStart and @dEnd" + CRLF+;

 " " + CRLF+ ;

 " select O.OrderId, Sum (Quantity) AS OrderQty, " + CRLF+;

 " Sum(od.UnitPrice * Od.Quantity * (1 - Od.Discount)) AS OrderTotal " + CRLF+;

 " From Orders O, [Order Details] OD " + CRLF+;

 " where O.orderId = OD.OrderId " + CRLF+;

 " and O.Orderdate between  @dStart and @dEnd" + CRLF+;

 " Group By O.OrderId"+ CRLF+;

 " Order By O.OrderId" +CRLF + ;

 " RETURN 1001 " +CRLF + ;

 " END " + CRLF

BEGIN SEQUENCE

       oConn:Execute(cProc, NIL,NIL)

END

RETURN