SqlQuery Stored Procedure that returns DataTable Example
This example calls a stored procedure that returns a datatable. The SqlServer version of pr_FriendlyNames uses the return value of the stored procedure to return the datatable, the Oracle version has a single output parameter that returns a Ref Cursor.
Notice that the name of the output parameter is "_result" under both SqlServer and Oracle even though you would expect the Oracle version of the stored procedure to return a value with the key "resultset". Because the stored procedure contains exactly 1 output Ref Cursor, the behaviour mimics that of SqlServer and returns the value with the key "_result".
| Smartsite SXML |
Copy Code
|
|---|---|
<se:sqlquery mode="storedprocedure" sql="pr_friendlynames" resulttype="datatable" save="t2">
<se:parameters>
<se:parameter name="params">
<se:collection>
<se:member name="action">info</se:member>
<se:member name="item">1</se:member>
<se:member name="updatecontents">1</se:member>
<se:member name="verbose">1</se:member>
</se:collection>
</se:parameter>
</se:parameters>
</se:sqlquery>
{debug.write($t2)}
|
|
| Example Result |
Copy Code
|
|---|---|
Name=_result; Value=SXMLDataTable (Columns=Row,Category,Description,Value) |
|