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

Below is a sample of how to call a stored procedure with 2 in parameters that results a resultset:

 

 

FUNCTION Start

 LOCAL oRs        AS AdoRecordSet

 LOCAL oConn AS AdoConnection

 LOCAL oCmd        AS AdoCOmmand

 LOCAL i        AS DWORD

 oConn := OpenConnection()

 // Create stored procedure

 CreateProc1(oConn)

 oCmd := AdoCommand{}

 oCmd:ActiveConnection := oConn

 oCmd:CommandText := "sp_OrderTotal"

 oCmd:CommandType := AdCmdStoredProc

 oRs := oCmd:Execute(NIL, {1996.07.01,1996.07.31},NIL)

 ? "Simple stored procedure with 2 IN paramaters"

 ? "List of results"

 ?

 DO WHILE ! oRs:EOF

 FOR i := 1 TO oRs:Fields:Count

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

 NEXT

 ?

 oRs:MoveNext()

 ENDDO

 wait

 

FUNCTION CreateProc1(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+ ;

       " AS 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

BEGIN SEQUENCE

       oConn:Execute(cProc, NIL,NIL)

END

RETURN