MSSQL Stored Procedure returning multiple Output Parameter values

questions
1

Hi all,

Does anyone call a stored procedure in MSSQL that uses multiple output fields. How do you handle that in Clarion. Are there any code examples?

Thanks
Ron

2

Ron, can you be more specific? Do you mean multiple output parameters or multiple result sets?

3

Rick,

Multiple output parameters.

4

I thought I had an example, but could only find a procedure with a single output parameter.
I whipped up a quick example based on that. Appears to work fine.

Stored procedure:

    CREATE PROC [dbo].[ReturnTwoOutputs]
    (
      @MyInput INT,
      @MyOutput1 INT OUTPUT,
      @MyOutput2 INT OUTPUT
    )
    AS
    BEGIN
      SET @MyOutput1 = @MyInput * 10;
      SET @MyOutput2 = @MyInput * 20;

    END;

Clarion code:

TestStoredProcOutput          procedure()
Input1                          long(4)
Output1                         long
Output2                         long
  code
  
  GBL:Owner = 'mirkwood\sql2016,SQLSupport'
  SEND(DummyTable,'/TRUSTEDCONNECTION=TRUE')
  open(DummyTable)
  if ERRORCODE()
    MESSAGE('open error: ' & choose(ERRORCODE() = 90, FILEERROR(), ERROR()))
    return
  end
  PUSHBIND()
  Bind('Output1',Output1)
  Bind('Output2',Output2)
  DummyTable{Prop:Sql} = 'NORESULTCALL dbo.ReturnTwoOutputs('& Input1 & ',&Output1[OUT]' & ',&Output2[OUT])'
  if ERRORCODE()
    MESSAGE('open error: ' & choose(ERRORCODE() = 90, FILEERROR(), ERROR()))
    close(DummyTable)
    return
  end
  POPBIND()
  MESSAGE('Input = ' & Input1 & ', output 1 = ' & Output1 & ', output 2 = ' & Output2)
  close(DummyTable)
  return

Output:

image

1 Like
5

Change NORESULTCALL to CALL if the procedure also returns a result set.

2 Likes
6

Rick,

Thank you. Great!

Ron