SQL Procedure returns an Autoit

[TzeaTzea]

Ok, if I have a procedure like this (just for example):

"

create [dbo].[procedure]

@v1 as int,

@v2 as int

AS

BEGIN

Select col from tbl where col=@v1

Select col from tbl where col=@v2

END

"

now in Autoit to get the procedure result:

#include <_sql.au3>
#include <array.au3>


Global $ServerIP = "127.0.0.1"
Global $ServerDataBase = "database"
Global $ServerUser = "user"
Global $ServerPass = "password"

_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

Global $db = _SQL_Startup()
If $db = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect($db, $ServerIP, $ServerDataBase, $ServerUser, $ServerPass) = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf

Local $aResult = "", $iRows = "", $iColumns = ""

_SQL_GetTable2D($db, "procedure 10,22", $aResult, $iRows, $iColumns)

_ArrayDisplay($aResult)

The _SQL_GetTable2D function will return only the first table. How do I retrieve the second table returned by the procedure?

Thx in advance

[jchd]

I really can't see how you can magically merge the result of two completely (and possibly very different) SELECT statements into one single resulting "object" (flat variable or array).

Secondly, I dont see the difference between your "procedure" and the equivalent SQL sequence (which is essentially portable accross engines):

_YourSQLInterface_GetTable2d($handle, "select col from table where col in ("&$value1&", "&$value2&") ;", $res1, $nbrows1, $nbcols1)

In which case the BEGIN .. COMMIT transaction is superfluous (most engine use autocommit mode for every single statement).

EDIT: sorry I missed that the variables in the proc were integral, so no need of string escaping here.

Edited November 3, 2010 by jchd