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