SQL Server Stored Procedure Example

BEGIN
  DECLARE @STATUS       INT
  DECLARE @instance_num INT
  DECLARE @cursor_num   INT
  DECLARE @child_cursor INT
  DECLARE @qual_count   INT
  DECLARE @customer_num INT
  DECLARE @company      VARCHAR(42)
  DECLARE @contact      VARCHAR(26)
  DECLARE @date_tickler datetime
  DECLARE @tickler      VARCHAR(4)
  DECLARE @initials     VARCHAR(6)
  DECLARE @action_info  VARCHAR(62)
  DECLARE @message      VARCHAR(256)
  DECLARE @counter      INT
 
  -- opening a flat file database from Transact-SQL  :)
  EXEC @STATUS = OdxConnectEnv 'd:\oadata\flat\orders\orders.dsn', @instance_num output
  IF @STATUS <> 0 GOTO exit_label
 
  -- open a cursor
  EXEC @STATUS = OdxOpenCursor @instance_num, @cursor_num output
  IF @STATUS <> 0 GOTO exit_label
 
  -- qualify rows using MDK indexes
  EXEC @STATUS = OdxParseSql @cursor_num, 'qualify customers where company = @kwd'
  IF @STATUS <> 0 GOTO exit_label
 
  -- bind the keyword
  EXEC @STATUS = OdxSetVariable @cursor_num, '@kwd', 'boe*'
  IF @STATUS <> 0 GOTO exit_label
 
  -- let's execute the sql and get the qualifying count
  EXEC @STATUS = OdxExecuteSql @cursor_num, @qual_count output
  IF @STATUS <> 0 GOTO exit_label
  print 'Rows qualified: ' + CONVERT(VARCHAR(32), @qual_count)
 
  -- let's show the qualified rows and its children rows
  EXEC @STATUS = OdxParseSql @cursor_num, 'select customer_no,company,contact from customers with odxid'
  IF @STATUS <> 0 GOTO exit_label
 
  -- define the column list
  EXEC @STATUS = OdxSetColumnType @cursor_num, 1, @customer_num
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @cursor_num, 2, @company
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @cursor_num, 3, @contact
  IF @STATUS <> 0 GOTO exit_label
 
  -- execute the select sql
  EXEC @STATUS = OdxExecuteSql @cursor_num
  IF @STATUS <> 0 GOTO exit_label
 
  -- open a second cursor for child table retrieval
  EXEC @STATUS = OdxOpenCursor @instance_num, @child_cursor output
  IF @STATUS <> 0 GOTO exit_label
 
  -- select rows from the child table
  EXEC @STATUS = OdxParseSql @child_cursor, 'select customer_no,date_tickler,tickler,initials,action_info from activity where customer_no = @cust_no order by date_tickler desc'
  IF @STATUS <> 0 GOTO exit_label
 
  -- define the column list for the child table  
  EXEC @STATUS = OdxSetColumnType @child_cursor, 1, @customer_num
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @child_cursor, 2, @date_tickler
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @child_cursor, 3, @tickler
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @child_cursor, 4, @initials
  IF @STATUS <> 0 GOTO exit_label
  EXEC @STATUS = OdxSetColumnType @child_cursor, 5, @action_info
  IF @STATUS <> 0 GOTO exit_label
 
  -- let's fetch the row from the parent table until the end
  EXEC @STATUS = OdxGetRow @cursor_num
  while @STATUS = 0
  BEGIN
 
    -- get the column values
    EXEC @STATUS = OdxGetColumn @cursor_num, 1, @customer_num output
    IF @STATUS <> 0 GOTO exit_label
    EXEC @STATUS = OdxGetColumn @cursor_num, 2, @company output
    IF @STATUS <> 0 GOTO exit_label
    EXEC @STATUS = OdxGetColumn @cursor_num, 3, @contact output
    IF @STATUS <> 0 GOTO exit_label
 
    -- display it
    EXEC @counter = OdxGetLastRowCount @cursor_num
    print CONVERT( VARCHAR(32), @counter) + ') ' + CONVERT( VARCHAR(32), @customer_num) + ' ' + @company + ' ' + @contact
 
    -- let's retrieve rows from the child table for this particular customer number
    EXEC @STATUS = OdxSetVariable @child_cursor, '@cust_no', @customer_num
    IF @STATUS <> 0 GOTO exit_label
    EXEC @STATUS = OdxExecuteSql @child_cursor
    IF @STATUS <> 0 GOTO exit_label
 
    -- fetch the child rows
    EXEC @STATUS = OdxGetRow @child_cursor
    while @STATUS = 0
    BEGIN
 
      -- get the child column values
      EXEC @STATUS = OdxGetColumn @child_cursor, 1, @customer_num output
      IF @STATUS <> 0 GOTO exit_label
      EXEC @STATUS = OdxGetColumn @child_cursor, 2, @date_tickler output
      IF @STATUS <> 0 GOTO exit_label
      EXEC @STATUS = OdxGetColumn @child_cursor, 3, @tickler output
      IF @STATUS <> 0 GOTO exit_label
      EXEC @STATUS = OdxGetColumn @child_cursor, 4, @initials output
      IF @STATUS <> 0 GOTO exit_label
      EXEC @STATUS = OdxGetColumn @child_cursor, 5, @action_info output
      IF @STATUS <> 0 GOTO exit_label
      -- display it
      print ' - ' + CONVERT( VARCHAR(32), @customer_num) + ' ' + CONVERT( VARCHAR(32), @date_tickler, 101) + ' ' + @tickler + ' ' + @initials + ' ' + @action_info
 
      EXEC @STATUS = OdxGetRow @child_cursor
    END
 
    EXEC @STATUS = OdxGetRow @cursor_num
  END
 
  IF @STATUS = 11032 SET @STATUS = 0
 
exit_label:
 
  IF @STATUS <> 0
  BEGIN
    EXEC OdxGetErrorMessage @STATUS, @message output
    print 'Error occured: ' + @message
  END
 
  -- clean up
  -- check if we need to close the cursors to avoid memory leak
  EXEC @STATUS = OdxIsOpened @child_cursor
  IF @STATUS <> 0
    EXEC @STATUS = OdxCloseCursor @child_cursor
  EXEC @STATUS = OdxIsOpened @cursor_num
  IF @STATUS <> 0
    EXEC @STATUS = OdxCloseCursor @cursor_num
 
  -- check if we need to disconnect to avoid memory leak
  EXEC @STATUS = OdxIsConnected @instance_num
  IF @STATUS <> 0
    EXEC @STATUS = OdxDisconnectEnv @instance_num
 
END
GO